Search code examples
mysqlsqlgroup-bygroup-concat

Grouping row on column and flattening the other columns

I have the following schema:

TABLE            ATTRIBUTES
---------------------------
movies           id
                 title

stars            id
                 first_name
                 last_name

genres           id
                 name

stars_in_movies  movie_id
                 star_id

genres_in_movies movie_id
                 genre_id

I want to create a query that returns a view that is grouped by movie.id and that concatenates the list of associated stars and genres

This is my attempt:

SELECT movies.id, title,
GROUP_CONCAT(stars.id separator ',') AS starIds,
GROUP_CONCAT(concat(first_name,' ',last_name) separator ',') AS starNames,
GROUP_CONCAT(genres.id separator ',') AS genreIds,
GROUP_CONCAT(genres.name separator ',') AS genreNames
FROM movies 
JOIN stars_in_movies ON stars_in_movies.movie_id = movies.id
JOIN stars ON stars_in_movies.star_id = stars.id
JOIN genres_in_movies ON genres_in_movies.movie_id = movies.id
JOIN genres ON genres_in_movies.genre_id = genres.id

For some reason, for each row in the view, the query concatenates the same star for each genre. For example here is an example of a row:

movie.id   movie.title      stars.id      genres.id
-----------------------------------------------------------
12345      Ocean's Twelve   3,3,3,4,4,4   2,4,6,2,4,6,2,4,6

EDIT:

This is the result of the query without using GROUP_CONCAT. It lists all combinations of tuples.

SELECT movies.id, stars.id, genres.id FROM movies
JOIN stars_in_movies ON stars_in_movies.movie_id = movies.id
JOIN stars ON stars_in_movies.star_id = stars.id
JOIN genres_in_movies ON genres_in_movies.movie_id = movies.id
JOIN genres ON genres_in_movies.genre_id = genres.id
WHERE movies.title = 'Oceans Twelve';

+--------+--------+--------+
|movies.id|stars.id|genres.id|
+--------+--------+--------+
| 764010 | 492108 | 907000 |
| 764010 | 492108 | 907001 |
| 764010 | 492108 | 855203 |
| 764010 | 492108 | 907002 |
| 764010 | 492108 | 150008 |
| 764010 | 492108 | 907005 |
| 764010 |  48004 | 907000 |
| 764010 |  48004 | 907001 |
| 764010 |  48004 | 855203 |
| 764010 |  48004 | 907002 |
| 764010 |  48004 | 150008 |
| 764010 |  48004 | 907005 |
| 764010 |  48014 | 907000 |
| 764010 |  48014 | 907001 |
| 764010 |  48014 | 855203 |
| 764010 |  48014 | 907002 |
| 764010 |  48014 | 150008 |
| 764010 |  48014 | 907005 |
| 764010 | 855108 | 907000 |
| 764010 | 855108 | 907001 |
| 764010 | 855108 | 855203 |
| 764010 | 855108 | 907002 |
| 764010 | 855108 | 150008 |
| 764010 | 855108 | 907005 |
| 764010 | 658011 | 907000 |
| 764010 | 658011 | 907001 |
| 764010 | 658011 | 855203 |
| 764010 | 658011 | 907002 |
| 764010 | 658011 | 150008 |
| 764010 | 658011 | 907005 |
| 764010 | 855106 | 907000 |
| 764010 | 855106 | 907001 |
| 764010 | 855106 | 855203 |
| 764010 | 855106 | 907002 |
| 764010 | 855106 | 150008 |
| 764010 | 855106 | 907005 |
| 764010 | 693109 | 907000 |
| 764010 | 693109 | 907001 |
| 764010 | 693109 | 855203 |
| 764010 | 693109 | 907002 |
| 764010 | 693109 | 150008 |
| 764010 | 693109 | 907005 |
+--------+--------+--------+
Solution

  • It looks like there is a Many to Many relationship between stars_in_movies and genres_in_movies. You need to perform your query step by step. First create a sub-query that groups on movies JOIN genres_in_movies. Then create a second sub query that groups on movies JOIN stars_in_movies. Then join between the sub queries on movie.id. This way you have a one to one relationship between the sub queries and you avoid the cartesian product that you currently have.