query involving one to many relationship

[Janine Dalton]

I am using a mysql database for a bookshop project i am working on. I am trying to run a select query to retrieve a list of book ISBNs, titles and authors that meet certain criteria.
My problem is this: I have set my database up so that there is a many to many relationship between authors and books, so I have 3 tables:
Books
-----
ISBN
Title
etc
Authors
-------
Author_ID
Forename
Surname
Books_Authors
-------------
ISBN
Author_ID
Now, when I run a select query like SELECT Books.ISBN, title, Forename, Surname FROM Books,Authors,Books_Authors WHERE Books.ISBN=Books_Authors.ISBN AND Authors.Author_ID=Books_Authors.Author_ID, I get all the information I need, but of course if a book has multiple authors, there are multiple rows in the result set.
When looping through the result set, I basically want to get each book's details and its authors and display them as *one* record for each book.
How can I tell whether the row I am processing is a "new book" or just contains details on another author for the previous book?
Or, would I be better off doing one query to get the list of ISBNs and titles etc and then for each ISBN retrieved, do a separate query to find the relevant author(s) for that ISBN? That seems a simpler approach to a newbie like me, but something tells me that using a whole load of queries instead of 1 is very inefficient!
Any advice would be greatly appreciated...

[Mark Howard]

Hi Janine
You should not have to separate your database reads to accomplish the result you are looking for.
If you sort your resultset by ISBN, your program could iterate through the resultset rows and treat each new ISBN encountered as a new book.
For example, if your resultset produced:

ISBN    Author
----    ------
112     M. Smith
112     R. Graham
115     D. Rose
116     F. Harvey
116     D. Rose
116     A. Morgan
116     M. Smith

you can, which simple looping logic, produce the following result:

Book    Author(s)
----    ---------
112     M. Smith, R. Graham
115     D. Rose
116     F. Harvey, D. Rose, A. Morgan, M. Smith

Please clarify if I have misunderstood your query
Mark.

[Janine Dalton]

Hi Mark,
Thanks for replying. Your example is exactly what I want to do but I'm afraid I can't see the "simple looping logic" required! Could you possibly elaborate a little?
while(rs.next()) is about my level

[Mark Howard]

Something like (in pseudocode):

Set tempISBN to null
While (there are more database records)
  If (tempISBN = databaseISBN)
    Add author details to existing book line
  Else
    Create new book line
    Set tempISBN to databaseISBN
  EndIf
EndWhile

Does this help?
[ March 17, 2003: Message edited by: Mark Howard ]

[Janine Dalton]

Thanks a lot Mark,
That's just what I need and that pseudo code looked very familiar, probably because I have used something similar in quite a few programs in the past!!! I think I've been spending far too much time staring at computer screens lately

[Mark Howard]

That's alright, I know the feeling...