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.