MSSQL 两个内部联接到同一个表

Question

我有一个列出一堆时间表(TK.细节(。

在该表中，它引用了员工 ID，我可以成功地使用它从员工表 (EMMain( 中提取名字和姓氏

传统知识。详细信息表还向我显示了一个项目 ID，我可以通过 INNER 加入项目表 (PR( 来获取该项目的主管 ID。

我正在尝试做的是连接 PR 和 EMMain 表以获取主管的名字和姓氏。

我

当前的查询为我提供了与员工 ID 相同的名字和姓氏。

我做错了什么。

   SELECT        tkDetail.Employee, tkDetail.WBS1, tkDetail.WBS2, tkDetail.LineItemApprovalStatus, tkDetail.LineItemApprover, PR.Supervisor, EMMain.LastName, EMMain.FirstName,EMMain.FirstName AS SupervisorFirst, EMMain.LastName AS supervisorlast
FROM            tkDetail INNER JOIN
                         PR ON tkDetail.WBS1 = PR.WBS1 INNER JOIN
                         EMMain ON tkDetail.Employee = EMMain.Employee INNER JOIN
                         EMMain AS EMPL ON PR.Supervisor = EMMain.Employee
WHERE (LineItemApprovalStatus is NULL)

Answer 1

请查看左连接是否有帮助，并注意在第三次连接时您应该再次使用 EMPL 别名而不是表名

     SELECT tkDetail.Employee, tkDetail.WBS1, tkDetail.WBS2, 
     tkDetail.LineItemApprovalStatus, tkDetail.LineItemApprover, 
     PR.Supervisor, EMMain.LastName, EMMain.FirstName,SUP.FirstName AS 
      SupervisorFirst, SUP.LastName AS supervisorlast 
FROM tkDetail 
INNER JOIN PR ON tkDetail.WBS1 = PR.WBS1 
left JOIN EMMain ON tkDetail.Employee = EMMain.Employee 
left JOIN EMMain SUP ON PR.Supervisor = SUP.Employee WHERE (LineItemApprovalStatus is NULL) 

Answer 2

没有数据很难说，但试着这样做

SELECT *
FROM tkDetail 
INNER JOIN PR ON tkDetail.WBS1 = PR.WBS1 
INNER JOIN EMMain PR.Supervisor = EMMain.Employee
WHERE (LineItemApprovalStatus is NULL)

Answer 3

也许它会像那样工作：

    SELECT tkDetail.Employee, tkDetail.WBS1, tkDetail.WBS2, tkDetail.LineItemApprovalStatus, tkDetail.LineItemApprover, PR.Supervisor, EMMain.LastName, EMMain.FirstName,EMMain.FirstName AS SupervisorFirst, EMMain.LastName AS supervisorlast
    FROM tkDetail INNER JOIN
     EMMain ON tkDetail.Employee = EMMain.Employee INNER JOIN 
     PR ON tkDetail.WBS1 = PR.WBS1 AND EMMain.Employee = PR.Supervisor  
    WHERE (LineItemApprovalStatus is NULL)