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