Re: difficult select problem

PJ wrote:

I've searched the web, the tutorials, etc. with no luck and have asked on MySql list with no luck and have even posted here with no replies. So, let's try again: I am trying to limit the search for books to only those that start with "A" (does not have to be case sensitive); but within that selection there may be a second author whose name may start with any letter of the alphabet. So, the real problem is to find if there is a second author in the selection of books and to display that name. My query shows all the books that begin with A: $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' ";

Within the results there are some books that have 2 authors and now I have to find if there is a second author in the results and then be able to echo the second author. So far, I have not been able to figure out how to go about that. Do I need to do another query to find the second author or can it somehow be incorporated into the original query? Or can it be done with a UNION ?

Union won't help because you don't know the results from the first query to put into the union. You could do it in one query but whether it's worth it is up to you to decide & test. I've removed the "book_publisher" stuff to make it easier to understand. Step 1: get the books the author has written. You don't need to join all of the tables here because you only want the bookid's. select bookid from book_author ba inner join authors a on (ba.authorid=a.id) where left (last_name, 1) = 'A' Step 2: Get all authors for those books: select ba.* from book_author ba inner join authors a on (ba.authorid=a.id) where ba.bookid in ( select

    bookid

from

    book_author ba
    inner join
    authors a on (ba.authorid=a.id)

where left (last_name, 1) = 'A' ); They should be inner joins, not left joins. If you need to join to other tables (book_publishers etc) don't do it in the inner query, do it in the outer (select ba.* stuff). -- Postgresql & php tutorials http://www.designmagick.com/