mysql - 使用 SELECT - JOIN 从同一个表中获取两个值

Question

我有三个表 - Material 、事件和单位。 Material 表和事件表都通过包含 unit_id 属性与单位表相关。如何通过单个查询同时获取事件单位和 Material 单位？

这是我当前的查询:

SELECT u.username, m.name, a.name, un.name
FROM users u
JOIN materials m
ON (m.id = u.material_id)
JOIN activities a
ON (a.id = u.activity_id)
JOIN units un
ON (un.id = m.unit_id OR un.id = a.unit_id)

但是我只返回一个单位，我想同时获得 Material 单位和事件单位。我如何实现这一目标？

Answer 1

两次加入units表

SELECT u.username, 
       m.name as m_name, unm.name as m_unit,
       a.name as a_name, una.name as a_unit
FROM users u
JOIN materials m ON m.id = u.material_id
JOIN activities a ON a.id = u.activity_id
JOIN units unm ON unm.id = m.unit_id
JOIN units una ON una.id = a.unit_id