sql - 使用连接构建复杂的 SQL 查询

Question

抱歉，标题不伦不类，但我不确定如何表述这个问题。假设我们有以下表格:

    people
    id|created_at|updated_at|name             |
    --|----------|----------|-----------------|
     1|          |          |Quentin Tarantino|
     2|          |          |Terry O'Quinn    | 
     3|          |          |Sam Jackson      |
     4|          |          |Michael Madsen   |
     5|          |          |Bryan Cranston   |

    crew_members
    id|created_at|updated_at|type    |
    --|----------|----------|--------|
     1|          |          |Actor   |
     2|          |          |Director|
     3|          |          |Writer  |

    movies
    id|created_at|updated_at|title         |
    --|----------|----------|--------------|
     1|          |          |Pulp Fiction  |
     2|          |          |Reservoir Dogs|

    series
    id|created_at|updated_at|title             |
    --|----------|----------|------------------|
     1|          |          |Lost              |
     2|          |          |Breaking Bad      |
     3|          |          |The Tarantino Show|

    credits
    id|created_at|updated_at|person_id|character   |crew_member_id|
    --|----------|----------|---------|------------|--------------|
     1|          |          |        1|            |             2|
     2|          |          |        1|Jimmy       |             1|
     3|          |          |        1|            |             3|
     4|          |          |        2|John Locke  |             1|
     5|          |          |        3|Jules       |             1|
     6|          |          |        4|Mr Blonde   |             1|
     7|          |          |        5|Walter White|             1|
     8|          |          |        1|Mr Brown    |             1|
     9|          |          |        1|Himself       |             1|

    credit_feature_person
    id|created_at|updated_at|person_id|credit_id|movie_id|series_id|
    --|----------|----------|---------|---------|--------|---------|
     1|          |          |        1|        1|       1|        0|
     2|          |          |        1|        2|       1|        0|
     3|          |          |        1|        3|       1|        0|
     4|          |          |        1|        1|       2|        0|
     5|          |          |        1|        8|       2|        0|
     6|          |          |        1|        3|       2|        0|
     7|          |          |        2|        4|       0|        1|
     8|          |          |        3|        5|       1|        0|
     9|          |          |        4|        6|       2|        0|
    10|          |          |        5|        7|       0|        2|
    11|          |          |        1|        9|       0|        3|

如果我想显示一个人(比如 ID 为 1 的 Tarantino)参与的所有电影，以及他们在那部电影中做了什么，我可以这样做:

    select m.title as movie, coalesce(character,type) as role 
    from "credits" 
    inner join "credit_feature_person" on "credits"."id" = "credit_id" 
    inner join "people" on "credit_feature_person"."person_id" = "people"."id" 
    inner join "movies" m on "credit_feature_person"."movie_id" = "m"."id"
    inner join "crew_members" on "credits"."crew_member_id" = "crew_members"."id"
    where "credits"."person_id" = 1;

    //result
         movie      |   role   
    ----------------+----------
     Pulp Fiction   | Director
     Pulp Fiction   | Jimmy
     Pulp Fiction   | Writer
     Reservoir Dogs | Director
     Reservoir Dogs | Mr Brown
     Reservoir Dogs | Writer
    (6 rows)

但我也想包括他们工作的系列，结果看起来像

         title      |   role   
    ----------------+----------
     Pulp Fiction   | Director
     Pulp Fiction   | Jimmy
     Pulp Fiction   | Writer
     Reservoir Dogs | Director
     Reservoir Dogs | Mr Brown
     Reservoir Dogs | Writer
    The Tarantino Show | Himself
    (7 rows)

我一直在尝试不同的事情，要么得到语法错误，要么得到 0 个结果。有任何想法吗？我希望至少能够做到这一点:

    select coalesce(m.title, s.title) as title, coalesce(character,type) as role 
    from "credits" 
    inner join "credit_feature_person" on "credits"."id" = "credit_id" 
    inner join "people" on "credit_feature_person"."person_id" = "people"."id" 
    inner join "movies" m on "credit_feature_person"."movie_id" = "m"."id"
    inner join "series" s on "credit_feature_person"."series_id" = "s"."id"
    inner join "crew_members" on "credits"."crew_member_id" = "crew_members"."id"
    where "credits"."person_id" = 1;

但这也不起作用，由于某种原因它返回零结果。

编辑:解决方案是对电影和连续剧使用左连接

select coalesce(m.title,s.title) as title, coalesce(character,type) as role 
from "credits" 
inner join "credit_feature_person" on "credits"."id" = "credit_id" 
inner join "people" on "credit_feature_person"."person_id" = "people"."id" 
left join "movies" m on "credit_feature_person"."movie_id" = "m"."id"
left join "series" s on "credit_feature_person"."series_id" = "s"."id"
inner join "crew_members" on "credits"."crew_member_id" = "crew_members"."id"
where "credits"."person_id" = 1; 

Answer 1

问题是电影和系列的链接是互斥的。当您尝试对缺少的每一行的一侧进行内部联接时，将从结果中消除。这两个连接需要是左外连接。

select coalesce(m.title, s.title) as title, coalesce(character,type) as role 
from "credits" 
    inner join "credit_feature_person" on "credits"."id" = "credit_id" 
    inner join "people" on "credit_feature_person"."person_id" = "people"."id" 
    inner join "crew_members" on "credits"."crew_member_id" = "crew_members"."id"
    left outer join "movies" m on "credit_feature_person"."movie_id" = "m"."id"
    left outer join "series" s on "credit_feature_person"."series_id" = "s"."id"
    where "credits"."person_id" = 1
order by title, role;

http://sqlfiddle.com/#!15/45440/5