List Many to Many results once each?

Discussion in 'ASP General' started by andrewrubie@hotmail.com, Aug 16, 2006.

  1. Guest

    Hi All,

    I have built a search page(asp) in dreamweaver for a friend with a used
    records store and website. The results page lists all recordings their
    database(ms access 2002) holds with(or similar to) the desired title
    entered by the user. The database uses a many to many relationship
    between tblArtists, tblLINKArtist_Recording and tblRecordings to allow
    every artist to have many recordings associated with them and every
    recording to have many artists associated with them(the example I use
    here is 'Nobody Told Me' which was recorded by John Lennon and Yoko
    Ono). Hence the 'Inner Join' used in the select statement shown below.

    "SELECT tblArtists.ArtistName, tblRecordings.RecordingID,
    tblRecordings.Title, tblRecordings.Price, FROM tblRecordings INNER JOIN
    (tblArtists INNER JOIN tblLINKArtist_Recording ON tblArtists.ArtistID =
    tblLINKArtist_Recording.ArtistID) ON tblRecordings.RecordingID =
    tblLINKArtist_Recording.RecordingID
    WHERE tblRecordings. Title Like'%Nobody Told Me%'"

    It works to return the record twice and is formatted in a search
    results page pretty much as follows:

    Title: End of the Line
    Artist: John Lennon
    Recording ID: 1066
    Price: $12

    Title: End of the Line
    Artist: Yoko Ono
    Recording ID: 1066
    Price: $12

    How can I get it to return that title(linked to the RecordingID) only
    once with the associated Artists listed in one of the fields as
    follows?

    Title: End of the Line
    Artist(s): Yoko Ono, John Lennon
    Recording ID: 1066
    Price: $12

    Any ideas?

    Regards,

    Penny.
     
    , Aug 16, 2006
    #1
    1. Advertising

  2. wrote:
    > Hi All,
    >
    > How can I get it to return that title(linked to the RecordingID) only
    > once with the associated Artists listed in one of the fields as
    > follows?
    >
    > Title: End of the Line
    > Artist(s): Yoko Ono, John Lennon
    > Recording ID: 1066
    > Price: $12
    >

    There's no easy way to do this from an external program. In an Access
    application, you could create a custom VBA function to do this, but
    custom VBA functions are not usable by external applications.
    What you could do is something similar to the solution posted here:
    http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/4a571433e343e624?hl=en&



    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Aug 16, 2006
    #2
    1. Advertising

  3. Guest

    Thanks for your response Bob.

    I see why you're suggesting to use an array. I've got a feeling however
    that I need the original recordset which returns all the titles and
    also a second recordset that runs for every single one of the results
    to see if there is more than one associated artist and if so to get the
    page to list them.

    If a page returns 500 resultant titles then the second recordset would
    have to be built 500 times!

    Does this make any sense?

    Penny

    Bob Barrows [MVP] wrote:
    > wrote:
    > > Hi All,
    > >
    > > How can I get it to return that title(linked to the RecordingID) only
    > > once with the associated Artists listed in one of the fields as
    > > follows?
    > >
    > > Title: End of the Line
    > > Artist(s): Yoko Ono, John Lennon
    > > Recording ID: 1066
    > > Price: $12
    > >

    > There's no easy way to do this from an external program. In an Access
    > application, you could create a custom VBA function to do this, but
    > custom VBA functions are not usable by external applications.
    > What you could do is something similar to the solution posted here:
    > http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/4a571433e343e624?hl=en&
    >
    >
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
     
    , Aug 17, 2006
    #3
  4. wrote:
    > Thanks for your response Bob.
    >
    > I see why you're suggesting to use an array. I've got a feeling
    > however
    > that I need the original recordset which returns all the titles and
    > also a second recordset that runs for every single one of the results
    > to see if there is more than one associated artist and if so to get
    > the
    > page to list them.
    >
    > If a page returns 500 resultant titles then the second recordset would
    > have to be built 500 times!
    >
    > Does this make any sense?
    >

    Not really.
    Just return all the records in a single recordset, sorted by RecordingID.
    Use GetRows to stuff the data into an array and make a single pass through
    the arraym keeping track of which RecordingID you are on and concatenating
    the names when needed. For example, your query returns 4 fields. Add an
    Order By clause to it so the records are sorted by RecordingID, open the
    recordset and process it as follows:

    dim conn, rs, sql, arData, curID, newID, Title, Price, Artists, i

    sql="SELECT tblRecordings.RecordingID,tblArtists.ArtistName, " & _
    "tblRecordings.Title, tblRecordings.Price, FROM tblRecordings " & _
    "INNER JOIN " & _
    "(tblArtists INNER JOIN tblLINKArtist_Recording " & _
    "ON tblArtists.ArtistID = tblLINKArtist_Recording.ArtistID) " & _
    "ON tblRecordings.RecordingID =" & _
    "tblLINKArtist_Recording.RecordingID " & _
    "WHERE tblRecordings. Title Like'%Nobody Told Me%' " & _
    "ORDER BY tblRecordings.RecordingID"
    'open conn here, then
    Set rs=conn.execute(sql,,1)
    If not rs.eof then arData=rs.GetRows
    rs.close:set rs=nothing
    'close and destroy conn here as well, unless you're not finished
    'with it ... then:
    if isArray(arData) then
    curID=arData(0,0)
    Title=arData(2,0)
    Price=arData(3,0)
    for i = 0 to ubound(arData,2)
    newID = arData(0,i)
    if newID <> curID then
    WriteRecord curID,Title,Artists,Price
    curID=newID
    Title=arData(2,i)
    Price=arData(3,i)
    Artists=""
    end if
    if len(Artists) > 0 then
    Artists = Artists & ", " & arData(1,0)
    else
    Artists = arData(1,0)
    end if
    next
    WriteRecord curID,Title,Artists,Price
    else
    'handle the no-records situation here
    end if

    Sub WriteRecord(pID,pTitle,pArtists ,pPrice)
    'Write the data to the Response
    End Sub


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Aug 17, 2006
    #4
  5. Guest

    It's becoming clearer now Bob. One more question however. If a
    recording does have more than one artist associated with it then the
    recording will still be listed more than once. Is there a way to filter
    out 'like' recordingIDs like the 'Unique Values' in a query properties
    or 'DISTINCT' in SQL might do?

    Regards,

    Penny

    Bob Barrows [MVP] wrote:
    > wrote:
    > > Thanks for your response Bob.
    > >
    > > I see why you're suggesting to use an array. I've got a feeling
    > > however
    > > that I need the original recordset which returns all the titles and
    > > also a second recordset that runs for every single one of the results
    > > to see if there is more than one associated artist and if so to get
    > > the
    > > page to list them.
    > >
    > > If a page returns 500 resultant titles then the second recordset would
    > > have to be built 500 times!
    > >
    > > Does this make any sense?
    > >

    > Not really.
    > Just return all the records in a single recordset, sorted by RecordingID.
    > Use GetRows to stuff the data into an array and make a single pass through
    > the arraym keeping track of which RecordingID you are on and concatenating
    > the names when needed. For example, your query returns 4 fields. Add an
    > Order By clause to it so the records are sorted by RecordingID, open the
    > recordset and process it as follows:
    >
    > dim conn, rs, sql, arData, curID, newID, Title, Price, Artists, i
    >
    > sql="SELECT tblRecordings.RecordingID,tblArtists.ArtistName, " & _
    > "tblRecordings.Title, tblRecordings.Price, FROM tblRecordings " & _
    > "INNER JOIN " & _
    > "(tblArtists INNER JOIN tblLINKArtist_Recording " & _
    > "ON tblArtists.ArtistID = tblLINKArtist_Recording.ArtistID) " & _
    > "ON tblRecordings.RecordingID =" & _
    > "tblLINKArtist_Recording.RecordingID " & _
    > "WHERE tblRecordings. Title Like'%Nobody Told Me%' " & _
    > "ORDER BY tblRecordings.RecordingID"
    > 'open conn here, then
    > Set rs=conn.execute(sql,,1)
    > If not rs.eof then arData=rs.GetRows
    > rs.close:set rs=nothing
    > 'close and destroy conn here as well, unless you're not finished
    > 'with it ... then:
    > if isArray(arData) then
    > curID=arData(0,0)
    > Title=arData(2,0)
    > Price=arData(3,0)
    > for i = 0 to ubound(arData,2)
    > newID = arData(0,i)
    > if newID <> curID then
    > WriteRecord curID,Title,Artists,Price
    > curID=newID
    > Title=arData(2,i)
    > Price=arData(3,i)
    > Artists=""
    > end if
    > if len(Artists) > 0 then
    > Artists = Artists & ", " & arData(1,0)
    > else
    > Artists = arData(1,0)
    > end if
    > next
    > WriteRecord curID,Title,Artists,Price
    > else
    > 'handle the no-records situation here
    > end if
    >
    > Sub WriteRecord(pID,pTitle,pArtists ,pPrice)
    > 'Write the data to the Response
    > End Sub
    >
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
     
    , Aug 17, 2006
    #5
  6. wrote:
    > It's becoming clearer now Bob. One more question however. If
    > a recording does have more than one artist associated with it
    > then the recording will still be listed more than once. Is
    > there a way to filter out 'like' recordingIDs like the 'Unique
    > Values' in a query properties or 'DISTINCT' in SQL might do?


    I see no reason why you could not perform two queries -- one to get a
    DISTINCT list of albums and another a DISTINCT list of artists. I certainly
    do not hesitate to perform multiple queries when it makes sense to do so.



    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms.
     
    Dave Anderson, Aug 17, 2006
    #6
  7. wrote:
    > It's becoming clearer now Bob. One more question however. If a
    > recording does have more than one artist associated with it then the
    > recording will still be listed more than once.


    Actually it won't. The WriteRecord sub only gets called once per
    RecordID.

    > Is there a way to
    > filter out 'like' recordingIDs like the 'Unique Values' in a query
    > properties or 'DISTINCT' in SQL might do?


    You could (in a separate query), but i see no need in this case.

    >


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Aug 17, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Blair
    Replies:
    1
    Views:
    420
    Eliyahu Goldin
    Aug 3, 2005
  2. tshad

    Printing multiple results at once

    tshad, May 17, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    346
    tshad
    May 17, 2006
  3. John Nagle
    Replies:
    1
    Views:
    215
    Gabriel Genellina
    Mar 22, 2009
  4. Chris Baker
    Replies:
    3
    Views:
    174
    Patrice
    Jan 3, 2006
  5. Gancy
    Replies:
    4
    Views:
    204
    Rasto Levrinc
    Feb 3, 2005
Loading...

Share This Page