2

Not sure how accurate my question title is but let me explain a little better.

I have two tables: artists, releases

table artist
  artist_id, 
  artist_name

table release
  release_id,
  release_artist_ids,
  release_name

There are more column names, but these are the ones involved in my problem.

release_artist_ids can contain one or more artist IDs that correspond to the artist table. If more than one artist features on the same release_id then I had comma separated the IDs.

So an example release row:

1 -- 2,5 -- Example Release

What I would like to do is list the releases and join the artist table to fetch the artist names involved in the release.

I managed to join using IN (release.release_artist_ids) but this only brings back one artist name. I would like to merge all involved artists into one returned column, separated by a pipe so I can format later on.

ain
  • 22,394
  • 3
  • 54
  • 74
user887515
  • 794
  • 3
  • 6
  • 19
  • 2
    You should really get rid of the comma separated IDs and properly normalize your tables. See this question for ideas: http://stackoverflow.com/q/7457643/723693 – ain Sep 17 '11 at 23:50

1 Answers1

4

I suggest that you change your database design to have a join table called release_artist that models the many-to-many relationship between artists and releases. This table should have two columns: release_id and artist_id.

If you can't change the database design, you can use FIND_IN_SET but it will be slow:

SELECT
   release.release_id,
   GROUP_CONCAT(artist.artist_name, '|') AS artist_names
FROM artist 
JOIN release
ON FIND_IN_SET(artist.id, release.release_artist_ids)
GROUP BY release.release_id
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452