LEFT JOIN across three tables (with junction table)

Question

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? 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. 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 ``` 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. 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…

Accepted Answer

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);
```

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;
```

LEFT JOIN across three tables (with junction table)

Solution

Problem

Related problems