Hi Lex, Thanks for responding. Indeed, it is a very difficult problem - for me, at least; I think it is as difficult to formulate the problem in plain English as to figure out how to solve it. My code already has selected the books whose authors last names start with A as well as the authors themselves. Within the results some books have 2 authors. The problem is: 1) to find if there is 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. Some observations inserted below: Lex Braun wrote: > PJ, > On Mon, Apr 6, 2009 at 12:32 PM, PJ <af.gourmet@xxxxxxxxxxxx > <mailto:af.gourmet@xxxxxxxxxxxx>> wrote: > > 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. > > > First off, are you trying to search for book TITLES that start with > "A" or book AUTHORS that start with "A"? > > If it's authors, it might make more sense to do a select that > retrieves the book ids that have an author whose last name starts with > an "A" and once you have those IDs, do a second query that will > retrieve all authors for those books. So something like below (UNTESTED): > > //Find out which books are written by an author with a last name > starting with $Auth > $SQL = "SELECT b.id <http://b.id> FROM book AS b > 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' "; > > Then do the following select statement with a foreach loop that > retrieves the book IDs above > > $query = "SELECT b.id <http://b.id>, GROUP_CONCAT(CONCAT(' ', > a.first_name, a.last_name) ORDER BY a.last_name) I believe this should be (CONCAT_WS(' ', a.first_name, a.last_name) or (CONCAT(a.first_name,' ', a.last_name) Problem here is that I need to use the ba.ordinal somewhere in the cod to distinguish between the authors - if ba.ordinal is 1, the author is listed as the first author and if there is no ba.ordinal = 2 for the ba.bookID, then there is only 1 author. I have not included this in my query because it only returns the ordinal number for the author which does not help me at all. I need to know who the second author is. In effect, it would be great if I could get 2 joins on the same table (author) for 1. (CONCAT(first_name, ' ', last_name) AS Author WHERE ab.ordinal = 1 && LEFT(last_name, 1) = $Auth 2. (CONCAT(first_name, ' ', last_name) AS Author1 WHERE ab.ordinal = 2 Of course, my example does not work; it is meant to give an idea of what I'm looking for. There must be a simple solution to this... but how simple is it to find it???? :-) Phil > FROM book AS b > 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 b.id <http://b.id> = $value > GROUP BY b.id <http://b.id> "; // $value is the b.id <http://b.id> > for each iteration of the foreach loop > > More information about the GROUP_CONCAT() function can be found in the > MySQL reference > (http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) > > - Lex -- 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
