Re: difficult select problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



PJ,

On Tue, Apr 7, 2009 at 11:37 AM, PJ <af.gourmet@xxxxxxxxxxxx> 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

[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux