Displaying One to Many Relationships

Results 1 to 5 of 5

Thread: Displaying One to Many Relationships

  1. #1
    JasonB Guest

    Default Displaying One to Many Relationships

    I am working on generating a report that will display a one to many relationship without redundancy.<BR><BR>Using an author/book example, here is my problem: For each book in the book table there may be several authors. As an author may have written other books or magazines, etc. the book table and the author table are seperate, but connected by a join table.<BR><BR>I&#039;m trying to display in a row the title of the book, and then all the authors linked to that book. I figured I could create two records sets one for the book and one for the author, and then loop through the authors displaying all the linked entries, but this solution seems awfully cumbersome. I also considered dumping all the authors into an array and then looping through the array. I&#039;m fairly new to all this, so any help would be appreciated. <BR><BR>Thanks, Jason

  2. #2
    RDM Guest

    Default Order By...

    If you run one query with the ORDER BY clause against the author, you can check for when the author changes as you iterate through the record set. Just use a variable to keep the current and last author. When the author changes, reset the variables and act accordingly.

  3. #3
    Join Date
    Dec 1969

    Default I really must put this in FAQs!

    I think this is the fourth time I&#039;ve answered this in a week.<BR><BR>(*NOT* a complaint to you, Jason! Not your fault! My own, for not making it into an FAQ.)<BR><BR>Anyway..<BR><BR>You are dead on re the database design! It&#039;s called NORMALIZATION, and it&#039;s amazing how few people "get it"! It&#039;s the entire POINT behind the word "Relational" in RDBMS.<BR><BR>Now...<BR><BR>How do you retrieve and display things? Like this:<BR><BR>&#060;%<BR>SQL = "SELECT books.bookName, authors.authorName " _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & "FROM books, authors " _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & "WHERE authors.bookID = books.bookID " _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & "ORDER BY books.bookName, authors.authorName"<BR><BR>Set RS = yourConnection.Execute( SQL )<BR><BR>priorBook = ""<BR><BR>Response.Write "&#060;UL&#062;" & vbNewLine<BR><BR>Do Until RS.EOF<BR>&nbsp; &nbsp; curBook = RS("bookName")<BR>&nbsp; &nbsp; If curBook &#060;&#062; priorBook Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; If priorBook &#060;&#062; "" Then Response.Write "&nbsp; &nbsp; &#060;/UL&#062;" & vbNewLine<BR>&nbsp; &nbsp; &nbsp; &nbsp; Response.Write "&#060;LI&#062;" & curBook & vbNewLine & "&nbsp; &nbsp; &#060;UL&#062;" & vbNewLine<BR>&nbsp; &nbsp; &nbsp; &nbsp; priorBook = curBook<BR>&nbsp; &nbsp; End If<BR>&nbsp; &nbsp; Response.Write "&nbsp; &nbsp; &#060;LI&#062;" & RS("authorName") & vbNewLine<BR>&nbsp; &nbsp; RS.MoveNext<BR>Loop<BR><BR>Response.Write "&#060;/UL&#062;&#060;/UL&#062;" & vbNewLine<BR>%&#062;<BR><BR>The vbNewLine&#039;s aren&#039;t necessary, but if you use them and the do a VIEW &#124 SOURCE in the browser, you&#039;ll find that even the HTML you generate is readable. Which makes finding bugs in the HTML a *LOT* easier.<BR><BR>Incidentally, this is much, much, much faster than the nested-query solution you posited. Yes, the info about each book is duplicated in each record of the recordset, which is not "free", but it really is worth the "cost", honest.<BR><BR>[ If you are getting *TONS* of info about each book, then you can do it with two recordsets (not nested, not one per book) and a "merge sort" done in the ASP code. But try it this way first. ]<BR><BR>

  4. #4
    JasonB Guest

    Default RE: I really must put this in FAQs!

    I forgot one last tricky point - I want to use orderable columns. This makes the ordering random so things don&#039;t clump together right.

  5. #5
    Join Date
    Dec 1969

    Default What is an orderable column???

    You mean the order of the columns changes on each book???<BR><BR>We can go offline with this. EMail me at billw@chilisoft.com<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts