我有两张桌子:历史票和用户
表1:历史门票
history_id | id_klien | id_staf | message
11 | 77 | NULL | text
12 | NULL | 1 | text
13 | 77 | NULL | text
表2:用户
user_id | name | group
1 | john | staff
77 | dion | member
我想要这样的结果:
history_id | message | user_id | name | group
11 | text | 77 | dion | member
12 | text | 1 | john | staf
13 | text | 77 | dion | member
最佳答案
SELECT a.HISTORY_ID,
a.MESSAGE,
COALESCE(a.ID_KLIEN, a.ID_STAF) UserID,
COALESCE(b.name, c.name) name,
COALESCE(b.group, c.group) `group`
FROM history_ticket a
LEFT JOIN users b
ON a.id_klien = b.user_ID
LEFT JOIN users c
ON a.id_staf = c.user_ID
SQLFIddle Demo
关于mysql - Mysql查询两个外键到一个主键,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/15027643/