MySQL Searching in Many-to-Many

Question

I've begun working on my first MySQL database, and I've run into a simple problem.

I have my movies categorized by genre in a 'many-to-many' relationship:

'movies' Table
+---------+-------------------+
|movie_id |movie_title        |
+---------+-------------------+
|1        |Indiana Jones      |
|2        |Shaun of the Dead  |
+---------+-------------------+

'genres' table
+---------+-----------+
|genre_id |genre_name |
+---------+-----------+
|1        |adventure  |
|2        |comedy     |
|3        |horror     |
+---------+-----------+

'movie_genres' table
+---------+---------+
|movie_id |genre_id |
+---------+---------+
|1        |1        |
|2        |2        |
|2        |3        |
+---------+---------+

What I am trying to do is search my movies table by genre, and display all the genres each movie is in, for instance:

Searching 'horror'

+------------------+---------------+
|movie_title       |genre_names    |
+------------------+---------------+
|Shaun of the Dead |comedy, horror |
+------------------+---------------+

Here is a query I would use to search for horror movies


    SELECT m.movie_title, GROUP_CONCAT(g.genre_name SEPARATOR ', ') as genre_names
    FROM movies m
    LEFT JOIN genre_movies gm ON gm.movie_id = m.movie_id
    LEFT JOIN genres g ON g.genre_id = gm.genre_id
    GROUP BY m.movie_id
    HAVING genre_names LIKE '%horror%'

The problem I have is that this query retrieves the entirety of all the tables before filtering it, which I gather is very inefficient. Is there a better way I could search by one genre while showing all associated genres?

score 3 · Accepted Answer · answered Nov 23 '12 at 07:09

3

Try this:

SELECT m.movie_title, A.genre_names
FROM movies m
INNER JOIN (SELECT gm.movie_id, GROUP_CONCAT(g.genre_name SEPARATOR ', ') AS genre_names
    FROM genre_movies gm INNER JOIN genres g ON g.genre_id = gm.genre_id
    GROUP BY gm.movie_id) AS A ON m.movie_id = A.movie_id 
WHERE genre_names LIKE '%horror%'

answered Nov 23 '12 at 07:09

Saharsh Shah

28,687
8
48
83

1

Huh, well that works! Thank you, I'll pick that apart until I understand what the heck you just showed me. – JProffitt Nov 23 '12 at 07:22

score 1 · Answer 2 · answered Nov 23 '12 at 07:05

1

Why don't you save the genre_id in the movie-table?

something like this:

'movies' Table
+---------+------------+---------------------+
|movie_id |movie_genre |movie_title          |
+---------+----------------------------------+
|1        |1           | Indiana Jones       |
|2        |2 ,3        | Shaun of the Dead   |
+---------+----------------------------------+

So you just need 2 tables then. And you can easily filter the data in the query.

What I am trying to do is search my movies table by genre, and display all the genres each movie is in, for instance:

Could be something like this:

SELECT fields FROM table WHERE movie_genre LIKE %movie_genre_id%

answered Nov 23 '12 at 07:05

F. Müller

3,969
8
38
49

I was originally going to do something like that, but after hearing about normalization and particularly why you shouldn't use [sets](http://www.vbmysql.com/articles/mysql/the-mysql-set-datatype) (as I was going to), I thought I should start on the right foot and learn about many-to-many tables. – JProffitt Nov 23 '12 at 07:12
guess it depends on the level of complexity you want to achieve - if you need your memory and or if you are having many, many datasets don't go for it. But else I think it would not be a big issue - it's up to you. – F. Müller Nov 23 '12 at 07:17
That's true. This is for a simple family project so it is unlikely it would run into any real issues. I happen to be a very lazy programmer though so I figure I should start out as rigid as possible when I have time to learn and then allow myself some leeway once I'm more comfortable using the language. :) – JProffitt Nov 23 '12 at 07:28
you're totaly right. It is always best to use the "proper" way - especially for learning. – F. Müller Nov 23 '12 at 07:32

score 1 · Answer 3 · answered Nov 23 '12 at 07:21

SELECT m.movie_title, GROUP_CONCAT(g.genre_name SEPARATOR ', ') as genre_names
FROM  movies m
      INNER JOIN
      (
        SELECT gm.movie_id
        FROM   genre_movies gm 
               INNER JOIN genres g 
                  ON g.genre_id = gm.genre_id
        WHERE  g.genre_name = 'horror'
        GROUP BY gm.movie_id
      ) a ON a.movie_id = m.movie_id
      INNER JOIN genre_movies gm ON gm.movie_id = m.movie_id
      INNER JOIN genres g ON g.genre_id = gm.genre_id
GROUP BY m.movie_id

SQLFiddle Demo

MySQL Searching in Many-to-Many

3 Answers3