This week's book giveaway is in the Mac OS forum. We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line! See this thread for details.
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...
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.
Joined: Jan 25, 2003
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
Joined: Feb 14, 2001
Something like (in pseudocode):
Does this help? [ March 17, 2003: Message edited by: Mark Howard ]
Joined: Jan 25, 2003
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