如何通过SELECT-JOIN同时获取材料与活动的关联单位信息

这个问题很常见——当你需要从同一个关联表中获取不同实体对应的关联数据时，重复JOIN目标表并使用别名区分是核心解决方案。

问题根源 #

你原来的查询用OR关联Units表，这会导致数据库仅返回满足任一条件的单位记录，无法区分该单位属于材料还是活动；甚至如果材料和活动对应不同单位，会产生不必要的笛卡尔积，返回多条重复的用户/材料/活动数据。

正确的查询语句 #

我们需要分别将Units表与Materials、Activities表进行关联，给Units表设置不同的别名来区分两个关联关系：

SELECT 
    u.username, 
    m.name AS material_name, 
    mu.name AS material_unit_name, 
    a.name AS activity_name, 
    au.name AS activity_unit_name
FROM users u
JOIN materials m ON m.id = u.material_id
JOIN activities a ON a.id = u.activity_id
JOIN units mu ON mu.id = m.unit_id  -- 关联材料对应的单位
JOIN units au ON au.id = a.unit_id  -- 关联活动对应的单位

代码解释 #

• 给Units表分别起别名mu（代表material units）和au（代表activity units），这样两次JOIN不会冲突。
• 在SELECT子句中明确指定mu.name和au.name，并通过别名material_unit_name、activity_unit_name区分显示，清晰展示各自对应的单位。

处理可能的空关联场景 #

如果存在用户未关联材料或活动的情况，可以将JOIN替换为LEFT JOIN，确保用户信息能正常返回，同时用COALESCE函数处理空值：

SELECT 
    u.username, 
    COALESCE(m.name, '无材料') AS material_name, 
    COALESCE(mu.name, '无单位') AS material_unit_name, 
    COALESCE(a.name, '无活动') AS activity_name, 
    COALESCE(au.name, '无单位') AS activity_unit_name
FROM users u
LEFT JOIN materials m ON m.id = u.material_id
LEFT JOIN units mu ON mu.id = m.unit_id
LEFT JOIN activities a ON a.id = u.activity_id
LEFT JOIN units au ON au.id = a.unit_id

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