Lex Braun wrote: > PJ, > > On Wed, Apr 1, 2009 at 1:40 PM, PJ <af.gourmet@xxxxxxxxxxxx > <mailto:af.gourmet@xxxxxxxxxxxx>> wrote: > > SELECT * FROM book > WHERE id IN (SELECT bookID > FROM book_author WHERE authID IN (SELECT author.id > <http://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 <http://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 > Thanks for that. I understand. Somehow, I had tried OR at one point but I got one of those orange boxes probably for some other error and in the confusion of my neurons I overlooked it. However, I am puzzled that using & (not &&) did give a lot of results, almost the whole db which is thankfully not very big for testing purposes. -- unheralded genius: "A clean desk is the sign of a dull mind. " ------------------------------------------------------------- Phil Jourdan --- pj@xxxxxxxxxxxxx http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
