Search code examples
mysqlsqlrdbms

How to avoid duplicate rows with junction table. (How to use it)?


Since a song can have several songwriters and a songwriter can write several songs I have created a junction-table songwriter_track. But I'm not sure how to "combine it" with my ordinary track table. I want to be able to select attributes from my track table and at the same time get the songwriters of the song. But unfortunately my current solution returns duplicates of songs. What I'm I doing wrong? Is there a way to solve this? Do I have to run two queries?

Here's my tables:

   TABLE track(
         track_id     INT PRIMARY KEY
         track_title       VARCHAR(100)
         track_duration    VARCHAR(100)
         lyric_url         VARCHAR(150))

  TABLE songwriter(
        songwriter_id INT  PRIMARY KEY
        songwriter_name VARCHAR(30)  
        UNIQUE INDEX (songwriter_name))

  TABLE songwriter_track(
        id_songwriter INT REFERENCES songwriter(songwriter_id)
        id_track      INT REFERENCES track(track_id)
        PRIMARY KEY (id_songwriter, id_track))


 VIEW `tracks_view` AS "
      "SELECT t.*, s.songwriter_name  "
           "FROM track t "
           "INNER JOIN songwriter_track st "
           "ON t.track_id = st.id_track "
           "INNER JOIN songwriter s "
           "ON s.songwriter_id = st.id_songwriter;";

Solution

  • If you want to generate just one record per track, one solution would be to put all the songwriters in a column as a comma-separated list.

    Consider:

    select 
        t.track_id, 
        t.track_title, 
        t.track_duration, 
        t.lyric_url,
        group_concat(s.songwriter_name order by s.songwriter_name) songwriters
    from track t
    inner join songwriter_track st on st.id_track = t.track_id
    inner join songwriter s on s.songwriter_id = sr.id_songwriter
    group by 
        t.track_id, 
        t.track_title, 
        t.track_duration, 
        t.lyric_url