PJ,

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

> 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'));
>

Say $Auth = "I", $Auth1 = "J", $Auth2 = "K".
The above sub-query is stating select author ids where the first digit in
last_name is I AND the first digit in last_name is J AND the first digit in
last_name is K.  For any given last_name (thus, author id), the first digit
can only be one of those options, thus this sub-query will return ZERO
results.

To get a sub-query that will only return results that start with I, J,
or K,you can instead use the following sub-query:
SELECT author.id FROM author WHERE LEFT(last_name, 1) = '$Auth' OR
LEFT(last_name, 1) = '$Auth1' OR LEFT(last_name, 1) = '$Auth2'

The above will return all author IDs where the surname starts with I, J, or
K.

- Lex

Reply via email to