Re: difficult select problem

PJ, On Tue, Apr 7, 2009 at 11:37 AM, PJ <af.gourmet@videotron.ca> wrote: > $SQL = "SELECT b.*, c.publisher, a.first_name, a.last_name > FROM book AS b > LEFT JOIN book_publisher as bp ON b.id = bp.bookID > LEFT JOIN publishers AS c ON bp.publishers_id = c.id > LEFT JOIN book_author AS ba ON b.id = ba.bookID > LEFT JOIN author AS a ON ba.authID = a.id > WHERE LEFT(last_name, 1 ) = '$Auth' "; > > (PLEASE LET ME KNOW IF THERE IS SOMETHING WRONG WITH THE CODE) > Let me try to clarify what I'm saying about your query. The above query will ONLY return authors who match the WHERE condition, thus have last name starting with A. This query will never find the second author for those books, unless that author's last name also starts with an A. That's why you first need to get a list of the book IDs that match your WHERE condition, and then grab the authors related to those book IDs (whether through two queries or using a sub-query). You've been shown several different ways of doing this through the responses provided. Do the provided queries not work for your test data? - Lex