Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > List Many to Many results once each?

Reply
Thread Tools

List Many to Many results once each?

 
 
andrewrubie@hotmail.com
Guest
Posts: n/a
 
      08-16-2006
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.

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-16-2006
http://www.velocityreviews.com/forums/(E-Mail Removed) 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.


 
Reply With Quote
 
 
 
 
andrewrubie@hotmail.com
Guest
Posts: n/a
 
      08-16-2006
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:
> (E-Mail Removed) 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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-17-2006
(E-Mail Removed) 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"


 
Reply With Quote
 
andrewrubie@hotmail.com
Guest
Posts: n/a
 
      08-17-2006
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:
> (E-Mail Removed) 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"


 
Reply With Quote
 
Dave Anderson
Guest
Posts: n/a
 
      08-17-2006
(E-Mail Removed) 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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-17-2006
(E-Mail Removed) 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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
"gem list" and "sudo gem list" yield different results Mike Nicholaides Ruby 3 03-28-2008 02:29 PM
Printing multiple results at once tshad ASP .Net 0 05-17-2006 04:56 PM
submitting form once results in multiple e-mails received...sometimes Chris Baker ASP General 3 01-03-2006 04:37 PM
How many ATA's at once? Johnathan Smith UK VOIP 3 08-19-2005 09:07 PM
Regex to match say char 't' exactly once in a string and no more than once Gancy Perl Misc 4 02-03-2005 02:19 PM



Advertisments