根据多个表的联接显示条件为true

Question

背景：

我想展示所有与演员本·阿弗莱克合作过的人。

这是我的数据：

People table

id    name

1     Ben Affleck
2     Leonardio DiCaprio
3     Matt Damon

Movies table
id   title
1    Good Will Hunting
2    example
3    example

Credits table

id  movie_id person_id 

1   1        1
2   2        4
3   3        6

所需输出

name         movie

Matt Damon   Good Will Hunting

这就是我尝试过的：

我知道我必须加入人物、电影和学分表，因为我正在努力寻找所有与本·阿弗莱克合作过的演员：

SELECT p.name, m.title, c.movie_id
FROM Credits c
JOIN People p ON p.id = c.person_id
JOIN Movies m ON m.id = p.id
WHERE p.name = 'Ben Affleck';

我还在研究这个。

Answer 1

或者您可以使用IN，它有时比EXISTS更不容易理解。您在最初的查询中加入Movies表时也出现了错误，您必须比较来自Movies的电影id(即id(和来自Credits的电影id，即movie_id

SELECT p.name, m.title
FROM Credits c
JOIN People p ON p.id = c.person_id
JOIN Movies m ON m.id = c.movie_id
WHERE m.id IN (
SELECT movie_id 
FROM Credits c1
JOIN People p1 on p1.id = c1.person_id
WHERE p1.name = 'Ben Affleck'
)

Answer 2

您可以使用exists:

select p.name, m.title, c.movie_id
from people p
inner join credits c on c.person_id = p.id
inner join movies m on m.id = c.movie_id
where exists (
select 1 
from people p1
inner join credits c1 on c1.person_id = p1.id
where c1.movie_id = c.movie_id and p1.name = 'Ben Affleck'
)

Answer 3

您可以使用IN子句，但不需要同时显示Ben Affleck

SELECT 
p.name, m.title, c.movie_id
FROM
Credits c
JOIN
People p ON p.id = c.person_id
JOIN
Movies m ON m.id = p.id
WHERE
m.id IN (SELECT 
movie_id
FROM
credis
WHERE
person_id IN (SELECT 
id
FROM
People
WHERE
name = 'Ben Affleck'))
AND p.name <> 'Ben Affleck'