So I have a MySQL database for a music library.
As I have several songs by the same artist, I have a table called Artists.
The Songs table links to the Artist table via an Artist_id.
Since a Song can have several different artists (or let's say 2 for a duet), I'm using XML to put the list of artist id's in. Here's what the data in Songs.Artist_ID's column looks like:
<artist_ids>
<artist_id>239780</artist_id>
<artist_id>784534</artist_id>
</artist_ids>
My question is simple. I've done queries by linking to the artist table via ExtractValue, but that only gets me the first one (or a specific one if I get specific with the XPath).
How can I search for or link with any/all of the artist_id's? I do not want to loop through SQL queries if I don't have to. Is there an easy way to say, here's a list of artists, go fetch the data on all of them?
This is the query I use now, and it gets it for the first artist only.
SELECT `tanoshimi`.`filename`, `linkArtists`.`name`, `linkSongs`.`title`
FROM `public_music`.`tanoshimi`, `public_music`.`linkArtists`, `public_music`.`linkSongs`
WHERE linkSongs.song_id = tanoshimi.song_id AND linkArtists.artist_id = ExtractValue(artist_ids, '/artist_ids/artist_id')
Any help on how to have multiple Artists listed in a single Column, but still searchable would be appreciated!
-Chris Joy