跨三个表的左联接(带联结表) [英] LEFT JOIN across three tables (with junction table)

问题描述

在Postgres中，是否有一种方法可以在由联结表链接的表之间执行left join，并对该链接表进行一些过滤?

In Postgres, is there a way to perform a left join between tables linked by a junction table, with some filtering on the linked table?

说，我有两个表，humans和pets，我想执行一个查询，查询中有人类ID和宠物的名字.如果存在人类ID，但是他们没有宠物叫，我仍然希望返回人类的行.

Say, I have two tables, humans and pets, and I want to perform a query where I have the human ID, and the pet name. If the human ID exists, but they don't have a pet with that name, I still want the human's row to be returned.

如果我有从pets到humans的FK关系，则可以:

If I had a FK relationship from pets to humans, this would work:

select h.*, p.*
from humans as h
left join pets as p on p.human_id = h.id and p.name = 'fluffy'
where h.id = 13

，然后我将获得有关人类13的详细信息以及蓬松的值的信息.另外，如果人类13没有一个名为蓬松"的宠物，我将获得一行包含人类13的值，并且该宠物的列为空值.

and I'd get a row with human 13's details, and fluffy's values. In addition, if human 13 didn't have a pet named 'fluffy', I'd get a row with human 13's values, and empty values for the pet's columns.

但，我没有直接的FK关系，我在humans和pets之间有一个联结表，所以我正在尝试类似的查询:

BUT, I don't have a direct FK relationship, I have a junction table between humans and pets, so I'm trying a query like:

select h.*, p.*
from humans as h
left join humans_pets_junction as j on j.human_id = h.id
left join pets as p on j.pet_id = p.id and p.name = 'fluffy'
where h.id = 13

这将返回人类13的所有宠物的行，除了蓬松的行外，其列为空.

Which returns rows for all of human 13's pets, with empty columns except for fluffy's row.

如果我在WHERE子句中添加p.name = 'fluffy'，则将所有空行过滤掉，但是这也意味着如果人类13根本没有一只名为蓬松的宠物，我将获得0行.

If I add p.name = 'fluffy' to the WHERE clause, that filters out all the empty rows, but also means I get 0 rows if human 13 doesn't have a pet named fluffy at all.

是否可以复制FK样式left join的行为，但是当与联结表一起使用时?

Is there a way to replicate the behavior of the FK-style left join, but when used with a junction table?

推荐答案

一种方法是在where子句中进行比较:

One method is to do the comparison in the where clause:

select h.*, p.*
from humans as h left join
     humans_pets_junction as j
     on j.human_id = h.id left join
     pets as p
     on j.pet_id = p.id and p.name = 'fluffy'
where h.id = 13 and (p.name = 'fluffy' or p.id is null);

或者，将联结表和pets表作为子查询或CTE:

Alternatively, join the junction table and the pets table as a subquery or CTE:

select h.*, p.*
from humans h left join
     (select j.*
      from humans_pets_junction j join
           pets p
           on j.pet_id = p.id and p.name = 'fluffy'
     ) pj
     on pj.human_id = h.id
where h.id = 13;

这篇关于跨三个表的左联接(带联结表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！