我有两张桌子:历史票和用户
表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/

10-12 12:21
查看更多