多对多关系中如何获取所有人员ID及对应符合条件的机构ID（无则为null）

数据表结构 #

person (id), person_agency (person_id, agency_id), agency(id, type)

原查询语句 #

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'  

问题说明 #

当前查询仅返回与类型为agency_type1的机构有关联的人员，但实际需求是获取所有人员ID列表：若存在符合条件的关联机构则显示其ID，不存在则显示null。

数据表示例数据 #

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

当前查询输出 #

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

期望输出 #

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

解决方案 #

问题根源在于where a.type = 'agency_type1'条件会过滤掉左连接后a.type为null的行（即无关联机构的人员）。正确做法是将过滤条件移至左连接的on子句中：

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 AND a.type = 'agency_type1'

补充说明 #

• 把类型筛选条件放到agency表的连接规则里，只会匹配符合类型的机构进行关联，不会剔除无关联的人员行，确保所有person数据都被保留。
• 若存在一个人员关联多个agency_type1机构的情况，上述查询会返回多行。如果需要每个人员仅显示一行（比如取首个符合条件的机构ID），可结合聚合函数分组：

select p.id, MAX(a.id) as agency_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 AND a.type = 'agency_type1'
group by p.id

内容的提问来源于stack exchange，提问作者Greg