Re: difficult select problem

On Mon, Apr 6, 2009 at 12:32 PM, PJ <af.gourmet@videotron.ca> 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 ? > Please help. > > > > -- > unheralded genius: "A clean desk is the sign of a dull mind. " > ------------------------------------------------------------- > Phil Jourdan --- pj@ptahhotep.com > 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 > > $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 last_name like '$Auth%' "; The above assumes that $Auth has a value of A and should pull all the authors whose last name starts with the letter A -- Bastien Cat, the other other white meat