Postgresql - How do I receive a pair of ids of entities in many-to-many relation with null value for the second id if condition is not met

Greg
August 18, 2022
336 views
0 votes
2 Answers

I’ve got the following tables: person (id), person_agency (person_id, agency_id) and agency(id, type)

this is my query:

select p.id, a.id from person p 
  left join person_agency pa on p.id = pa.person_id
 left join agency a on pa.agency_id = a.id
  where a.type = 'agency_type1'

However, with the query I get only the persons who have a relation with an agency of "agency_type1". Instead, I would like to get a list of ids of ALL persons with ids of agencies, where the relation exists and null where it doesn’t. I tried naive outer joins but it did not work.

For this content of the tables:

Person:

+-------+
|  id   |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
+-------+

Person_agency:

+-----------+-----------+
| person_id | agency_id |
+-----------+-----------+
|         1 |         1 |
|         1 |         2 |
|         2 |         4 |
|         4 |         5 |
+-----------+-----------+

Agency:

+--------+------------------+
|     id |    type          |
+--------+------------------+
|      1 |  agency_type1    |
|      2 |  some_other_type |
|      3 |  agency_type1    |
|      4 |  agency_type1    |
|      5 |  some_other_type |
+--------+------------------+

I receive the folloing output of my query:

+----------+------+
|     p.id | a.id |
+----------+------+
|        1 |    1 |
|        2 |    4 |
+----------+------+

The desired output would be:

+----------+------+
|     p.id | a.id |
+----------+------+
|        1 | 1    |
|        2 | 4    |
|        3 | null |
|        4 | null |
+----------+------+

Answers

- LevAri
- August 18, 2022 at 1:41 pm
- 0 votes
0
Try to change left join
to
join (inner join).

Login or Signup to reply.

- jjanes
- August 19, 2022 at 3:16 am
- 0 votes
0
It looks like you don’t want to distinguish between an agency which is missing and an agency which is present but the wrong type. So you would want a regular JOIN not a LEFT JOIN for the pa/a pair, and also want to filter out the unwanted type directly on that join. Then you want to do a LEFT JOIN from person to the results of that just-described join.
```
select p.id p_id, a.id a_id from person p 
left join (person_agency pa join agency a on pa.agency_id = a.id and a.type='agency_type1')
on p.id = pa.person_id;

 p_id |  a_id  
------+--------
    1 |      1
    2 |      4
    3 | (null)
    4 | (null)
```
The parenthesis around the join pair are not necessary but I find they make it clearer.

If one person is associated to multiple agencies of the correct type, all of them will be shown. I assume this is what you want, although it was not a scenario covered in your example data.
Login or Signup to reply.