MySQL join 获取用户信息

Question

我的表 A 中有 2 个不同的用户 ID(Originator 和Assigned_To)，并且

一个单独的表 B，其中包含 ID、名字、姓氏等。

表 A(问题)

  Ticket | Originator | Assigned_To
  TKT1234  abc452       kshd736
  TKT1235  abc453       kshd738
  TKT1236  abc454       kshd739

表B(用户表)

  User ID | First Name | Last Name 
  abc452     John        Smith
  kshd736    Mary          J
  abc453     A           Sharma
  kshd738   Brad         Smith

所需结果表:

  Ticket | Originator Name    | Assigned_To_Name 
  TKT1234  John Smith(abc452)    Mary J(kshd736)
  TKT1235  A Sharma(abc453)      Brad Smith(kshd738)
  ....
  ....

如何连接这两个表以获得组合的(名称 + ID)字段。我尝试了下面的方法，但这不是:

   (select  ticket_id,
concat(e.first_name,e.last_name, " (", a.originator, ")")  as originator , 
  concat(e.first_name,e.last_name, " (", a.issue_assigned_to, ")")  as 
 assigned_to 
 FROM ISSUE a
 LEFT JOIN DPDM_ISSUE_REVISION_RELATIONSHIP b ON a.name = b.issue
   LEFT JOIN DPDM_REVISION c ON b.reported_against_revision = c.name
   LEFT JOIN DPDM_MASTER d ON c.master = d.name
    left join USER e on a.originator = e.user_id 
 where a.lifecycle_state not in ("Closed") and ( c.name like '%HVBMS%' OR c.name like '%BCC6%'));

有人可以指导一下吗？谢谢!

Answer 1

您的查询似乎是一些与您提出的问题相关的随机 SQL。我假设你想做这样的事情:

SELECT a.ticket, 
  CONCAT(b.first_name, " ", b.last_name, " (", a.originator,")") as originator_name, 
  CONCAT(c.first_name, " ", c.last_name, " (", a.assigned_to,")") as assigned_to_name
FROM ISSUE a
LEFT JOIN USER b ON a.originator = b.user_id
LEFT JOIN USER c ON a.assigned_to = c.user_id

希望这有帮助。