Unexpected result from JOIN and GROUP_CONCAT with three tables

Question

here's an link to the schema and sql: http://sqlfiddle.com/#!9/d99b6/8

Schema:

create table movies (
  id int,
  movie_name varchar(20)
  );

create table genre (
  movie_id int,
  genre varchar(20)
  );

create table actors (
  movie_id int,
  actor_name varchar(20)
  );

insert into movies (id, movie_name)
  values
    (1, 'asdf');

insert into genre (movie_id, genre)
  values
    (1, 'Comedy'),
    (1, 'Thriller');

insert into actors (movie_id, actor_name) 
  values
    (1, 'actor1'),
    (1, 'actor2');

Sql:

select movies.id,
  movies.movie_name,
  group_concat(genre.genre separator ', ') as genre,
  group_concat(actors.actor_name separator ', ') as actors
from movies
inner join genre on genre.movie_id = movies.id
inner join actors on actors.movie_id = movies.id;

I want to output this:

id    movie_name    genre               actors
1     asdf          Comedy, Thriller    actor1, actor2

But get this instead:

id    movie_name    genre                                 actors
1     asdf          Comedy, Thriller, Comedy, Thriller    actor1, actor1, actor2, actor2

Not sure how to go about this problem, help would be appreciated.

You should include sample data in your question, along with the query. SQL Fiddle is not always up. — Gordon Linoff, Oct 18 '15 at 14:54

score 0 · Accepted Answer · answered Oct 18 '15 at 14:56

You can use `distinct:

select m.id, m.movie_name,
       group_concat(distinct g.genre separator ', ') as genres,
       group_concat(distinct a.actor_name separator ', ') as actors
from movies m inner join
     genre g
     on g.movie_id = m.id inner join
     actors a
     on a.movie_id = m.id
group by m.id;

If you have lots of data for each movie, then the distinct adds extra overhead. The joins do a Cartesian product of genres and actors for each movie. You can handle that by pre-aggregating or using correlated subqueries:

select m.id, m.movie_name,
       (select group_concat(g.genre separator ', ')
        from genre g
        where g.movie_id = m.id 
       ) as genres,
       (select group_concat(a.actor_name separator ', ')
        from actors a
        where a.movie_id = m.id
       ) as actors
from movies m;

score 0 · Answer 2 · answered Oct 18 '15 at 14:59

Just add DISTINCT:

select movies.id,
  movies.movie_name,
  group_concat(DISTINCT genre.genre separator ', ') as genre,
  group_concat(DISTINCT actors.actor_name separator ', ') as actors
from movies
inner join genre on genre.movie_id = movies.id
inner join actors on actors.movie_id = movies.id;

SqlFiddleDemo

Unexpected result from JOIN and GROUP_CONCAT with three tables

2 Answers2