MySQL search in many ways

Question

I started working on my first MySQL database and I had a simple problem.

I have films classified 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'm trying to do is search in my table of films by genre and display all genres in which each movie is located, for example:

  Searching 'horror'

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

Here is a query that I would like to 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 genre_names LIKE '% horror%' 

The problem is that this query retrieves the entire set of all tables before filtering it, which I find very inefficient. Is there a better way I could search for one genre by showing all related genres?

Answer 1

Try the following:

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%' 

Answer 2

Why don't you save the genre_id to the kiosk?

something like that:

 '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. And you can easily filter the data in the query.

What I'm trying to do is search in my film by genre and display all the genres in which each film is located, for example:

Maybe something like this:

 SELECT fields FROM table WHERE movie_genre LIKE %movie_genre_id% 

Answer 3

 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