Join all link table entries in one single row

Question

I've been trying to get a query which would allow me to put all music genres of an event (Link table) in a single row but I have been quite unsuccessful so far; it constantly returns 2 rows with the same information about the event in each but changing the music genre entries at the LEFT JOIN. Here's my query:

SELECT 
    events.*
    , music_genres_link.* 
FROM events 
LEFT JOIN music_genres_link 
    ON events.id = music_genres_link.event_id 
WHERE events.id=1 
ORDER BY date DESC

And here's what it returns:

Phpmyadmin Screenshot

How do I get these two rows together in a single one? I need both the genre_title and genre_id columns.

I'd like to get the whole event row and left join all the music genres found on the link table to the left of the result, as such:

[event result] [music_genre_1 (id, title)] [music_genre_2 (id, title)] etc

This is not a complete question. Please include sample data for both of the original tables. We can't really decompose your result set link (don't post links). — Tim Biegeleisen, Jul 24 '18 at 14:08

dimo raichev · Answer 1 · 2018-07-24T14:18:06.940

1

It seems that you have 2 rows that match the join in music_genres_link. You can see that from the two 'gender_id' values - 5 and 295.

Ok now that you have edited your question, you want something that is not typical for SQL. You can achieve that with PIVOT but you dont have limit for the amount of genres and i wouldn't recommend it. You need to rethink your tables and what you want to achieve.

edited Jul 24 '18 at 14:18

answered Jul 24 '18 at 14:03

dimo raichev

570
5
15

Indeed, I do. But how do I get them in a single row? – Iuri Dias Jul 24 '18 at 14:04
Whell one way is to choose the one you want and to add it in the `where` or `join` clause – dimo raichev Jul 24 '18 at 14:05
Dimo raichev is absolutely right - you have to define first: how should they be combined in one row? You want to keep one row and get rid of the other one? You want to get them combined via comma-seperated value lists? There are multiple possibilities ... – Grimm Jul 24 '18 at 14:06
1

@IuriDias your data doesn't seem consistent why do you have 2 gender types with different IDs and same names – dimo raichev Jul 24 '18 at 14:06
@dimoraichev It's a reference to a JSON file I have on the server. – Iuri Dias Jul 24 '18 at 14:09
@Grimm It doesn't really matter, I simply want all the results found on the music_genres_link table to output to a single row, be it comma seperated or not. – Iuri Dias Jul 24 '18 at 14:10

score 1 · Accepted Answer · answered Jul 24 '18 at 14:46

I suspect you just want group_concat():

SELECT e.*, GROUP_CONCAT(mgl.genre_title) as genres
FROM events e LEFT JOIN
     music_genres_link mgl
     ON e.id = mgl.event_id 
WHERE e.id = 1 
GROUP BY e.id;

This assumes that you want the genre_title only. If you want the genre_id, then you can add another column.

Join all link table entries in one single row

2 Answers2