Hi Bastien, Thanks for the suggestion. But my code already does that. I think it is as difficult to formulate the problem in plain English as to figure out how to solve it: The problem is: Within the results some books have 2 authors: 1) is there a second author for any of the books in the results 2) find and echo the second author. So far, I have not been able to figure out how to go about that without a lot of queries. Bastien Koert wrote: > > > On Mon, Apr 6, 2009 at 12:32 PM, PJ <af.gourmet@xxxxxxxxxxxx > <mailto:af.gourmet@xxxxxxxxxxxx>> 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 <http://b.id> = > bp.bookID > LEFT JOIN publishers AS c ON bp.publishers_id = c.id > <http://c.id> > LEFT JOIN book_author AS ba ON b.id <http://b.id> = ba.bookID > LEFT JOIN author AS a ON ba.authID = a.id <http://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@xxxxxxxxxxxxx <mailto: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 > > > $SQL = "SELECT b.*, c.publisher, a.first_name, a.last_name > FROM book AS b > LEFT JOIN book_publisher as bp ON b.id <http://b.id/> = bp.bookID > LEFT JOIN publishers AS c ON bp.publishers_id = c.id <http://c.id/> > LEFT JOIN book_author AS ba ON b.id <http://b.id/> = ba.bookID > LEFT JOIN author AS a ON ba.authID = a.id <http://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 -- 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
