Many-to-many query, MySQL

Question

I have 3 tables like this:

movies:

• movie_id (int)
• movie_title (varchar)
• movie_runtime (int)
• movie_releaseYear (int)
• ...

genres:

• genre_id (int)
• genre_name (varchar)

movieGenres:

• movieGenre_id (int)
• movieGenre_movieId (int)
• movieGenre_genreId (int)

A movie can include many genres.
I want to grab the movie title, runtime and release year together with its genres. How can I do that in a single query?

I tried to do it like this:

SELECT u.movie_title, u.movie_runtime, u.movie_releaseYear, a.genre_name 
FROM movieGenres ua 
LEFT JOIN movies u ON u.movie_id = ua.movieGenre_filmId 
LEFT JOIN genres a ON a.genre_id = ua.movieGenre_genreId
LIMIT 0,10

This return something like:

Robin Hood _ 101 _ 2001 _ Adventure
Robin Hood _ 101 _ 2001 _ Action
Robin Hood _ 101 _ 2001 _ Drama
Firewall _ 98 _ 2003 _ Action
Firewall _ 98 _ 2003 _ Drama

I don't want to repeat the information I already know. I want it to display like this:

Robin Hood _ 101 _ 2001 _ Adventure _ Action _ Drama
Firewall _ 98 _ 2003 _ Action _ Drama

Please help me figure this out:)

Answer 1

I had this problem a couple of weeks ago, and my professor gave me a pretty clever solution:

Use the Group_Concat function in a sub query to get the results as comma separated values, then use explode/split/whatever language equivalent to loop through the CSV results.

http://mahmudahsan.wordpress.com/2008/08/27/mysql-the-group_concat-function/

Hope this helps! This problem had me frustrated for awhile, but I think this is the simplest solution.

Answer 2

SELECT
    u.movie_title,
    u.movie_runtime,
    u.movie_releaseYear,
    GROUP_CONCAT(a.genre_name) genre
FROM
    movieGenres ua  
    INNER JOIN movies u ON u.movie_id = ua.movieGenre_filmId  
    INNER JOIN genres a ON a.genre_id = ua.movieGenre_genreId
GROUP BY
    u.movie_title,
    u.movie_runtime,
    u.movie_releaseYear
LIMIT 0,10 
;

This is essentially your query. I just added the GROUP BY clause and GROUP_CONCAT function. The default delimiter is a comma(,). If you want the list of genres space-separated, do this with the GROUP_CONCAT function:

SELECT
    u.movie_title,
    u.movie_runtime,
    u.movie_releaseYear,
    GROUP_CONCAT(a.genre_name SEPARATOR ' ') genre
FROM
    movieGenres ua  
    INNER JOIN movies u ON u.movie_id = ua.movieGenre_filmId  
    INNER JOIN genres a ON a.genre_id = ua.movieGenre_genreId
GROUP BY
    u.movie_title,
    u.movie_runtime,
    u.movie_releaseYear
LIMIT 0,10
;