Trying to mysql group concat elements from 3 different tables

Im having trouble making a single mysql query that groups some items..

Visualized is this:

Table Show:

ID Movie IndexID
1 This 1234
2 That 1235

Table Show_index

IndexID Genre_ID
1234 2
1234 3
1235 1
1235 4

Table Show_genres

Genre_ID Genre_Name
1 One
2 Two
3 Three
4 Four

How do I get a mysql query that shows me this?

Show Genres
This Two,Three
That One,Four

This can be done using group by and group_concat() :

select Movie as `show`, group_concat(Genre_Name) as Genres
from `show` s
inner join Show_index i on s.IndexID = i.IndexID
inner join Show_genres g on g.Genre_ID = i.Genre_ID
group by ID, Movie
order by ID

Demo here

Leave a Comment