I have a list of songs on albums. In some cases the same song is on more than one album, and I'm wondering if there's a simple way to indicate that in a query? For example, by adding an asterisk.
So, I have two tables:
Code:
Songs {
songID (autonumber)
title (text)
}
lp_contents {
SongKeyID (autonumber),
song_id (number)
LP_id (number)
}
To find all the songs on album #1076 I can query:
Code:
SELECT Songs.Title, LPContents.LP_id, LPContents.song_id
FROM Songs INNER JOIN LPContents ON Songs.SongID = LPContents.song_id
WHERE LPContents.LP_id=1076;
LPContents can have multiple references to the same song, so in this list, songs 6 & 10 show up twice.
Code:
SongKeyID song_id LP_id
1 6 1076
2 6 20
3 7 1076
4 8 1076
5 9 20
6 10 1076
7 10 20
Is there a way to put an asterisk next to any song_id that exists more than once in LPContents? So that a list of songs from album 1076 could display as:
Code:
Songs.Title LPContents.song_id
(*)song 1 6
song 2 7
song 3 8
(*)song 5 10
Is such a thing possible within SQL? Or will I have to create a function so I can do something like:
Code:
SELECT IsDoubled(LPs.song_id), Songs.Title, LPContents.LP_id, LPContents.song_id
FROM Songs INNER JOIN LPContents ON Songs.SongID = LPContents.song_id
WHERE LPContents.LP_id=1076;