我有这样的erd:
erd
我希望所有演员都与一位特定的董事一起工作。
这是我的查询:
SELECT
p.first_name, p.last_name, COUNT(r.movie_id) as working_number
FROM
person p
LEFT JOIN
roles r ON r.person_id = p.id
WHERE
r.movie_id IN (SELECT
r.movie_id
FROM
roles r
WHERE
role_type = 'Director'
AND person_id = (SELECT
id
FROM
person
WHERE
first_name = 'Quentin'
AND last_name = 'Tarantino'))
and r.role_Type = "Actor"
GROUP BY p.id;
这将检索与工作数量超过1的演员。
我也想要其他演员,即使有0个工作时间。
您需要多个左连接,两次person
和role
:
SELECT p.first_name, p.last_name, COUNT(pd.id) as working_number
FROM person p
LEFT JOIN role r ON r.person_id = p.id AND r.role_Type = 'Actor'
LEFT JOIN role rd ON rd.movie_id = r.movie_id AND rd.role_type = 'Director'
LEFT JOIN person pd ON pd.id = rd.person_id AND pd.first_name = 'Quentin' AND pd.last_name = 'Tarantino'
GROUP BY p.id, p.first_name, p.last_name
您不应在Where子句中使用左JOIN表列..以这种方式,JOIN工作作为内在加入
尝试将这些条件添加到子句上的左键
SELECT p.first_name
, p.last_name
, COUNT(r.movie_id) as working_number
FROM person p
LEFT JOIN roles r ON r.person_id = p.id
AND r.movie_id IN (
SELECT r.movie_id
FROM roles r
WHERE role_type = 'Director'
AND person_id = (
SELECT id
FROM person
WHERE first_name = 'Quentin'
AND last_name = 'Tarantino')
)
and r.role_Type = "Actor"
GROUP BY p.first_name, p.last_name;