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

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?

From stackoverflow

• 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.

  Yegor : That seems to work. Is there a way to also get a 2nd parameter from the DB (cat_id) and pass them at the same time through an array?

  derobert : you could add another group_concat(c.cat_id) to the select list. Other than that, not sure what you mean.