List Many to Many results once each?

[andrewrubie]

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.

[Bob Barrows [MVP]]

an*********@hotmail.com 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/micro...343e624?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.

[andrewrubie]

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:

an*********@hotmail.com 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/micro...343e624?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]]

an*********@hotmail.com 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"

[andrewrubie]

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:

an*********@hotmail.com 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"

[Dave Anderson]

an*********@hotmail.com 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.

[Bob Barrows [MVP]]

an*********@hotmail.com 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.