On Wed, Apr 1, 2009 at 1:27 PM, PJ <af.gour...@videotron.ca> wrote:

> I am trying to select all books whose authors last names begin with I, J
> or K. I have 3 tables: book, author and book_author. The following query
> works with one condition but not with three.
> SELECT * FROM book
>        WHERE id IN (SELECT bookID
>        FROM book_author WHERE authID IN (SELECT author.id
>        FROM author WHERE LEFT(last_name, 1 ) = 'I'));
>
> This does not work:
> SELECT * FROM book
>        WHERE id IN (SELECT bookID
>        FROM book_author WHERE authID IN (SELECT author.id
>        FROM author WHERE LEFT(last_name, 1 ) = 'I' && LEFT(last_name, 1
> ) = 'J' && LEFT(last_name, 1 ) = 'K')) ";
>
> But this produces irrational results - there are no author names with
> the last names starting with I, J or K.
> SELECT * FROM book
>        WHERE id IN (SELECT bookID
>        FROM book_author WHERE authID IN (SELECT author.id
>        FROM author WHERE LEFT(last_name, 1 ) = '$Auth' &
> LEFT(last_name, 1 ) = '$Auth1' & LEFT(last_name, 1 ) = '$Auth2')) ";
>
> I'm a little lost here.
> Could somebody explain, please?
>
> Maybe the LIKE operator would be sufficient:

SELECT * from book
WHERE
last_name LIKE "I%" OR
last_name LIKE "J%" OR
last_name LIKE "K%";

David

Reply via email to