On Mon, Apr 6, 2009 at 2:05 PM, PJ <af.gourmet@xxxxxxxxxxxx> wrote: > 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. If you already have the book IDs where this happens, then for each bookID (probably a foreach loop in your code) simply do a query at that point that will obtain ALL authors for that bookID -- don't include the condition that last name start with an A in this query. > $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) > Sorry, yes that should be GROUP_CONCAT( CONCAT(' ', a.first_name, a.last_name) ORDER BY a.last_name) so that it concatenates each author as "FirstName LastName" and then groups all authors for a given book separated by commas, so bookID=1 would have something like "Author1FirstName LastNameAuthor1", "FirstNameAuthor2 LastNameAuthor2" 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 The problem you'd run into with #1 above, is you'd only get authors whose last name starts with "A" if they're the primary author. Is that the desired functionality you're looking for? - Lex
