How do I receive a pair of ids of entities in many-to-many relation with null value for the second i-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

Try to change left join to join (inner join).