Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

query involving one to many relationship

 
Janine Dalton
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

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

Please clarify if I have misunderstood your query
Mark.
 
Janine Dalton
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Something like (in pseudocode):

Does this help?
[ March 17, 2003: Message edited by: Mark Howard ]
 
Janine Dalton
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's alright, I know the feeling...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic