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
