mysqlsqljoinassociative

SQL Listing multiple artists for a song

I suspect my question has a duplicate and I am sorry for that as I couldn't find what I'm looking for. If that is the case, could you tell me where I can find that answer?

I want to manage a song library.

I have a table called songs :

song_id    song_name
====================
1          Airplanes
2          Relax

I have a table called artists :

artist_id    artist_name
============================
1            B.o.B
2            Hayley Williams
3            Mika

and to allow a song to have multiple artists, I have a table called assoc :

song_id    artist_id
====================
1          1
1          2
2          3

I want to SELECT all the entries in songs with the associated artists, like so :

Airplanes => B.o.B, Hayley Williams

Relax => Mika

So far, here is what I tried :

SELECT `songs`.`song_name`, `artists`.`artist_name`
FROM `assoc`
JOIN `songs` ON `songs`.`song_id`=`assoc`.`song_id`
JOIN `artists` ON `artists`.`artist_id`=`assoc`.`artist_id`

But this is giving me duplicate entries for songs with multiple artists. Is there a way to get a new field listing the artists, say, comma separated?

Solution

  • You can use GROUP_CONCAT

    SELECT `s`.`song_id`,
       `s`.`song_name`
        GROUP_CONCAT(`a`.`artist_name`) `artists`
FROM `assoc` 
JOIN `songs` s ON `s`.`song_id`=`assoc`.`song_id`
JOIN `artists` a ON `a`.`artist_id`=`assoc`.`artist_id`
GROUP BY `s`.`song_id`,
         `s`.`song_name`

    Be ware of that fact it has a default limit of 1024 characters to concat but it can be increased which is defined in manual

    Fiddle Demo