mysql - 在同一个表中查找与给定其他行具有相同多对多关系的行

Question

表结构如下。

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

一部电影可以映射多个流派，所以在 tbl_movie_genre 的情况下存在多对多关系。

我有电影详细信息页面，我可以在其中获取电影 ID。我必须编写查询来完成以下任务。

我必须为给定的电影 ID 找到所有匹配流派的电影。

例如我有电影 ID 45，这部电影属于喜剧和浪漫类型，然后我必须找到所有其他类型为喜剧和浪漫的电影。不应填充只有喜剧或只有浪漫的电影。

是否可以在单个查询中完成？

更新:

在下面的示例中，只有前 2 部电影满足条件。 “Hum tum ke pyar”和“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)

Answer 1

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 = ?)

而 ? 是给定的电影 ID