Finding rows in the same table which have the same many-to-many relationships as given other row

Following is table structure.

• tbl_movie_master (movie_id,title)
• tbl_genre_master (genre_id,genre)
• tbl_movie_genre (movie_id, genre_id)

One movie can be mapped with multiple genres, so there is many to many relationship in case of tbl_movie_genre.

I have movie details page where I am getting a movie ID. I have to write query to do following task.

I have to find movies with ALL matching genres for the given movie ID.

for e.g. I have movie ID 45 and this movie belongs to genre Comedy AND Romance, then I have to find all other movies with genre Comedy AND Romance. Movies with only Comedy or only Romance should not be populated.

Is it possible to do it in single query?

Update:

In the following example, Only the first 2 movies satisfy the criterion. "Hum tum ke pyar" and "housefull".

drop table if exists tbl_movie_master, tbl_genre_master, tbl_movie_genre ;

create table tbl_movie_master (movie_id int, title varchar(100));
insert into tbl_movie_master values (1, 'Hum tum ke pyar'), (2, 'housefull'), (3, 'ek vakta ke liye'), (4, 'chalo pyar kare');

create table tbl_genre_master (genre_id int, genre varchar(100));
insert into tbl_genre_master values (1, 'horror'), (2, 'remoance'), (3, 'suspense'), (4, 'social');

create table tbl_movie_genre (movie_id int, genre_id int);
insert into tbl_movie_genre values (1, 1), (1, 2), (2, 1), (2, 2), (3, 3), (4, 1), (4,4);

mysql> select * from tbl_movie_master;
+----------+------------------+
| movie_id | title            |
+----------+------------------+
|        1 | Hum tum ke pyar  | 
|        2 | housefull        | 
|        3 | ek vakta ke 开发者_如何学Pythonliye | 
|        4 | chalo pyar kare  | 
+----------+------------------+
4 rows in set (0.00 sec)

mysql> select * from tbl_genre_master;
+----------+----------+
| genre_id | genre    |
+----------+----------+
|        1 | horror   | 
|        2 | remoance | 
|        3 | suspense | 
|        4 | social   | 
+----------+----------+
4 rows in set (0.00 sec)

mysql> select * from tbl_movie_genre;
+----------+----------+
| movie_id | genre_id |
+----------+----------+
|        1 |        1 | 
|        1 |        2 | 
|        2 |        1 | 
|        2 |        2 | 
|        3 |        3 | 
|        4 |        1 | 
|        4 |        4 | 
+----------+----------+
7 rows in set (0.00 sec)


SELECT m.*
FROM tbl_movie_master m
JOIN tbl_movie_genre mg ON mg.movie_id = m.movie_id
WHERE mg.genre_id = ALL (SELECT mg.genre_id FROM tbl_movie_genre mg WHERE mg.movie_id = 1);

Empty set (0.00 sec)

SELECT m.title 
FROM tbl_movie_master m
JOIN tbl_movie_genre allgenres 
  ON m.movie_id= allgenres.movie_id
JOIN 
  (SELECT genre_id FROM tbl_movie_genre WHERE movie_id=1) somegenres
  ON somegenres.genre_id=allgenres.genre_id;

+-----------------+
| title           |
+-----------------+
| Hum tum ke pyar | 
| Hum tum ke pyar | 
| housefull       | 
| housefull       | 
| chalo pyar kare | 
+-----------------+
5 rows in set (0.00 sec)

SELECT  M.`title`, G.`genre`, M2.`title`
FROM    tbl_movie_master AS M, tbl_movie_genre AS A
LEFT JOIN tbl_movie_master AS M2 ON A.`movie_id` = M2.`movie_id`
LEFT JOIN tbl_genre_master AS G ON A.`genre_id` = G.`genre_id`
WHERE   M.`movie_id` = 1;

+-----------------+----------+------------------+
| title           | genre    | title            |
+-----------------+----------+------------------+
| Hum tum ke pyar | horror   | Hum tum ke pyar  | 
| Hum tum ke pyar | remoance | Hum tum ke pyar  | 
| Hum tum ke pyar | horror   | housefull        | 
| Hum tum ke pyar | remoance | housefull        | 
| Hum tum ke pyar | suspense | ek vakta ke liye | 
| Hum tum ke pyar | horror   | chalo pyar kare  | 
| Hum tum ke pyar | social   | chalo pyar kare  | 
+-----------------+----------+------------------+
7 rows in set (0.00 sec)

SELECT m.*
FROM movie m
JOIN movie_genre mg ON mg.movie_id = m.id
WHERE mg.id = ALL (SELECT mg.genre_id FROM movie_genre mg WHERE mg.movie_id = ?)

whereas ? is the given movie id

try:

SELECT m.title 
FROM tbl_movie_master m
JOIN tbl_movie_genre allgenres 
  ON m.movie_id= allgenres.movie_id
JOIN 
  (SELECT genre_id FROM tbl_movie_genre WHERE movie_id=45) somegenres
  ON somegenres.genre_id=allgenres.genre_id;

This question shouts for JOINs.

Something like this will work:

SELECT  M.`title`, G.`genre`, M2.`title`
FROM    tbl_movie_master AS M, tbl_movie_genre AS A
LEFT JOIN tbl_movie_master AS M2 ON A.`movie_id` = M2.`movie_id`
LEFT JOIN tbl_genre_master AS G ON A.`genre_id` = G.`genre_id`
WHERE   M.`movie_id` = A.`movie_id`
            AND M.`title` = "Your Movie";

Need to add "group by having" to dnagirl's answer to get the exact same number of categories. I guess this is what I was looking for...

SELECT mg1.movie_id,
       mm1.title,
       COUNT(*) AS cnt
FROM   tbl_movie_genre AS mg1
       INNER JOIN tbl_movie_master AS mm1
         ON mg1.movie_id = mm1.movie_id
       INNER JOIN (SELECT genre_id
                   FROM   tbl_movie_genre
                   WHERE  movie_id = 1) AS tt1
         ON mg1.genre_id = tt1.genre_id
GROUP  BY movie_id
HAVING cnt = (SELECT COUNT(*) AS mycnt
              FROM   tbl_movie_genre
              WHERE  movie_id = 1);