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

Question

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

Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect justified by reference to authoritative documentation or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Aug 18 '22 at 11:45
LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is a faq. But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. PS [More.](https://stackoverflow.com/a/55111083/3404097) — philipxy, Aug 18 '22 at 11:46
Your WHERE clause turns the outer join to `agency` back into an inner join — , Aug 18 '22 at 11:47
Possible duplicate of [Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) — philipxy, Aug 18 '22 at 12:12
@philipxy I do not really understand what is meant with minimal reproducible example. Should i provide actual data, like fake person ids and fake agencies with different types? I tried different left and right joins and I haven't got the right result. — Greg, Aug 18 '22 at 13:30
How is it you cannot understand what is written at the link [mre]? Here it is in again not hidden under other text: https://stackoverflow.com/help/minimal-reproducible-example — philipxy, Aug 18 '22 at 13:40
indeed I do not understand. I wrote what the data structure is, I wrote example data, even though it is redundant, I wrote what I tried and then i wrote what I got and what I wanted. so what am I missing or what is there too much? — Greg, Aug 18 '22 at 14:24
I tried in both cases left join, full, outer join in all combinations and I tried replacing where with and in a join clause - to no avail. so I thought it was necessary to write what the problem is and what has been tried to solve the problem. I described it minimally and it is reproducible. — Greg, Aug 18 '22 at 14:28
@jjanes is it now good enough? not sure, however, how to make the actual and desired output less horrible. — Greg, Aug 18 '22 at 18:55

score 1 · Accepted Answer · answered Aug 19 '22 at 01:16

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.

Thank you that you got back and answered. it does the job. – Greg Aug 19 '22 at 08:03 — Greg, Aug 19 '22 at 08:03

LevAri · Answer 2 · 2022-08-18T11:41:32.103

-2

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

edited Aug 18 '22 at 11:41

answered Aug 18 '22 at 11:41

LevAri

11
2

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

2 Answers2