How do you fix this mysql query without the use of a subquery?

Question

I have 3 tables. For the purposes of this example I will simplify it as much as I can.

First table contains movie ids and names, second table contains genre ids and genre names (action, drama, etc). 3rd table stores the genre id associated with each movie, since some movies have more than one. It has 2 columns, genre_id and movie_id. Fairy simple stuff. Im trying to output the list of movies, along with a list of genres associated with each movie.

    SELECT *
     FROM movies
     LEFT JOIN gen_pairs
     ON movies.mov_id = gen_pairs.gen_movieid
     LEFT JOIN categories
     ON gen_pairs.gen_catid = categories.cat_id 
    GROUP BY mov_id

This will obviously output a single genre of each film, even if it has multiple ones in the gen_pairs table. How would I get it to display a comma separated list of genres for each movie, without running a sub-query for each item?

Answer 1

Your select should build the Cartesian product, so you'll get output like

MovieA   GenreA
MovieA   GenreB
MovieB   GenreA
...

But it sounds like you want this instead:

MovieA   GenreA, GenreB
MovieB   GenreA
...

MySQL has a GROUP_CONCAT function to do what you want:

SELECT m.mov_id, GROUP_CONCAT(c.name)
       FROM movies m
  LEFT JOIN gen_pairs gp ON (m.mov_id = gp.gen_movieid)
  LEFT JOIN categories c ON (gp.gen_catid = c.cat_id)
  GROUP BY m.mov_id

Note the GROUP_CONCAT and GROUP BY.