aspose file tools*
The moose likes JDBC and the fly likes query involving one to many relationship Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "query involving one to many relationship" Watch "query involving one to many relationship" New topic
Author

query involving one to many relationship

Janine Dalton
Greenhorn

Joined: Jan 25, 2003
Posts: 4
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

Joined: Feb 14, 2001
Posts: 285
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

Joined: Jan 25, 2003
Posts: 4
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

Joined: Feb 14, 2001
Posts: 285
Something like (in pseudocode):

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

Joined: Jan 25, 2003
Posts: 4
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

Joined: Feb 14, 2001
Posts: 285
That's alright, I know the feeling...
 
jQuery in Action, 2nd edition
 
subject: query involving one to many relationship