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;";
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