Displaying one to many data

Results 1 to 3 of 3

Thread: Displaying one to many data

  1. #1
    Join Date
    Dec 1969

    Default Displaying one to many data

    Displaying one to many data<BR><BR>What is the most efficient way of displaying one to many relational data?<BR>For example I want to display a few categories and in each category there are many articles.<BR>Table one -&#062; Categories<BR>Table two -&#062; articles<BR>and a one to many relationship between the two tables<BR><BR>The way it is done currently:<BR>-While not eof of Categories<BR>-Display Category heading<BR>-Open articles where Articles.Categry_ID = Cateries.Category_ID<BR>-While not eof of rsArticles <BR>-Display article<BR>-loop<BR>-loop<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Displaying one to many data

    One idea:<BR><BR>Open the recordset with all of the articles you will use, apply a filter, and then move through records.<BR><BR>That way only one recordset is created.<BR>pseudocode follows<BR>For Each Category in Articles<BR>objRS.Filter = "Category = " & Category<BR> Do While NOT objRS.EOF<BR> Response.Write objRS("article_info")<BR> objRS.MoveNext<BR> Loop<BR>Loop

  3. #3
    Join Date
    Dec 1969

    Default The easy (and fast!) way...

    First, you use a JOIN to get the data from *both* tables. You make sure you use ordering to keep all subcategories of a category "clustered" in the result.<BR><BR>Secondly, you simply output a new category header anytime the category changes!<BR><BR>Since you can *EVEN* do this using ADODB.RecordSet.GetRows, you can get some pretty decent efficiency out of it, too! (Troy&#039;s idea of using a Filter will work, but it means processing the raw recordset multiple times. No, I do *not* know how much of a penalty on performance that is...it might not be much, but it&#039;s not free, either.)<BR><BR>SO... the following code does *not* use GetRows, so it&#039;s not quite as efficient as it could be. But it&#039;s not bad. If you really *need* maximal efficiency and don&#039;t know how to use GetRows, ask. (But if this isn&#039;t a really busy web site, don&#039;t worry about it. Most any scheme would work.)<BR><BR>&#060;%<BR>SQL = "SELECT C.CatName, S.SubCatName, S.ProductID " _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & "FROM Categories AS C, Subcategories AS S " _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & "WHERE S.CatID = C.CatID " _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & "ORDER BY C.CatName, S.SubCatName"<BR><BR>Set RS = yourConnection.Execute( SQL )<BR><BR>priorCat = ""<BR><BR>Response.Write "&#060;UL&#062;" & vbNewLine<BR>Do Until RS.EOF<BR>&nbsp; &nbsp; curCat = RS("CatName")<BR>&nbsp; &nbsp; If curCat &#060;&#062; priorCat Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; If priorCat &#060;&#062; "" Then Response.Write "&#060;/UL&#062;" & vbNewLine<BR>&nbsp; &nbsp; &nbsp; &nbsp; Response.Write "&#060;LI&#062;" & curCat & vbNewLine _<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; & "&nbsp; &nbsp; &#060;UL&#062;" & vbNewLine<BR>&nbsp; &nbsp; &nbsp; &nbsp; priorCat = curCat<BR>&nbsp; &nbsp; End If<BR>&nbsp; &nbsp; Response.Write "&nbsp; &nbsp; &#060;LI&#062;" & RS("SubCatName") & ": " & RS("ProductID") & vbNewLine<BR>Next<BR><BR>Response.Write "&#060;/UL&#062;" & vbNewLine<BR><BR>RS.Close<BR>%&#062;<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