What about using "IN", something like: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_authors WHERE authID IN (SELECT author.id FROM authors WHERE last_name LIKE "$Auth%")); You could use LEFT instead of LIKE, too. -----Original Message----- From: PJ [mailto:af.gourmet@xxxxxxxxxxxx] Sent: Tuesday, March 31, 2009 2:06 PM To: Jim Lucas Cc: Chris; php-general@xxxxxxxxxxxxx Subject: Re: formulate nested select Gentlemen & all others, The problem was partly fixed with ' ' around $Auth... but... somehow, I see that the results do not work with the rest of the script. The results give a an array within an array - so this mucks up all the rest which is set up to deal with only the book table. The count() is off as it shows the results_per_page setting and the pagination is off - it shows 10 books but only displays 5 entries (from 5 arrays which, I suppose is the reason for the coun() showing 10. The first page shows 5, but the second indicates 7 books but displays only 6... Now, I suppose that there are 2 ways to fix things: 1. Redo the rest of the script (a royal pain, I suspect) or 2. SELECT only the books that are attributed to the targeted authors - which is what I wanted to do in the first place. Something like: $SQL = "SELECT * FROM book b WHERE b.id = (SELECT book_author.bookID WHERE book_author.authID = (SELECT author.id WHERE LEFT(author.last_name, 1 ) = '$Auth')"; I want to avoid joins as that seems to screw up the rest of the code which is in an include page that needs to be repeated as long as there are letters in the alphabet. I'll try to figure something out, but as somebody not too optimistic once said: "it sure don't look too good" (American, I believe...) :-) > Jim Lucas wrote: >> Chris wrote: >>> PJ wrote: >>>> I cannot find anything on google or the manuals/tutorials that >>>> gives some kin of clear explanation of how to so nested selects >>>> with where or whatever. >>>> I have three tables: books, authors and book-authors. >>>> I need to retrieve only those books whose author's names begin with A. >>>> I have tried several maniipulations of where and select with select >>>> subqueries and I cannot get results from the queries. >>>> For example >>>> "SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE >>>> LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID && b.id >>>> = c.bookID ....<snip> >>> Not really a php question :P >>> >>> You don't need a subquery for this. You can join all of the tables >>> together and just use the where clause to cut down your results, but >>> I'll give an example of both. >>> >>> select * >>> from >>> books b inner join book_authors c on (b.id=c.bookId) inner join >>> authors a on (a.id=c.authorId) where left(a.last_name = 'A'); >> correct me if I'm wrong, but did you use the left() function >> in-correctly? >> >> The documentation shows a different way to use it then you describe. >> >> Something more like the following: >> >> WHERE >> LEFT(a.last_name, 1) = 'A'; >> >> But that would be case-sensitive... >> >> So, something like this would work better IMHO >> >> WHERE >> UPPER(LEFT(a.last_name, 1)) = 'A'; >> >> or >> >> WHERE >> a.last_name ILIKE 'A%'; >> >> would do the trick >>> or >>> >>> select * >>> from >>> books b inner join book_authors c on (b.id=c.bookId) where >>> c.authorId in ( select id from authors where left(last_name='A') >> Again... >> >> SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A') >> >> but yet again, case-sensitive... >> >> SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or >> SELECT id FROM authors WHERE last_name ILIKE 'A%' >> >> would do the trick >> >>> ); >>> >> >> > Thank you for the suggestions, gentlemen. > As to the case sensitivity, since the authors' names must be written > with the first letter in uppercase, even "Anonymous" or "Unknown" I > assume I don't need to specify uppercase. Or does it really make a > difference? Glad to learn of the option, though. > > I'm just starting on the listing of the books by author and just > realized that the sorting should be by author (last name). Can I > foresee a problem in that since the last_name is in associative tables > and not in the book table? Or does the JOIN incorporate the last_name > in the results? -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
