Find rows in the same table that have the same several-to-many relationships as the other row

advertisements

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 liye |
|        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)


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;