SQL Server - 两个连接到同一列上的同一个表

Question

我很好奇，如果可以连接两个表来重新获取像下面这样的东西。

**superTable** 

id | project 
1  aaa   
2  bbb  

**approvedTable** 
id | statusStep | Emp 
1  BossMan Gregor 
1  FirstMate Hans 

**notApprovedTable** 
id | statusStep | Emp 
1  Sailor  Julia 
1  Sr.Sailor Dave 

我想加入这两个表“approvedTable”和“notApprovedTable”以超表上的ID，以有这样的事情

id | project | approvedStep | Emp | notApprovedStep | Emp 
    1  aaa  BossMan  Gregor  null   null 
    1  aaa  FirstMate  Hans   null   null 
    1  aaa  null   null   Sailor  Julia  
    1  aaa  null   null   Sr.Sailor  Dave    

，如果我做一个简单连接就建立这样的

id | project | approvedStep | Emp | notApprovedStep | Emp 
    1  aaa  BossMan  Gregor  Sailor  Julia 
    1  aaa  FirstMate  Hans   Sailor  Julia 
    1  aaa  BossMan  Gregor  Sr.Sailor  Dave 
    1  aaa  FirstMate  Hans   Sr.Sailor  Dave 

Answer 1

UNION可以帮助你

SELECT s.ID,s.project,a.statusStep approvedStep,a.Emp,NULL AS notApprovedStep, NULL AS notApprovedEmp 
FROM superTable s 
INNER JOIN approvedTable a ON s.ID=a.ID 
UNION 
SELECT s.ID,s.project,NULL AS approvedStep,NULL AS Emp,na.statusStep AS notApprovedStep,na.Emp AS notApprovedEmp 
FROM superTable s 
INNER JOIN notApprovedTable na ON s.ID=na.ID 

Answer 2

查询两个查询结果UNION ALL：

select s.id, s.project, 
     a.statusstep as approved_step, a.emp as approved_emp, 
     null as not_approved_step, null as not_approved_emp 
from supertable s 
join approvedtable a on a.id = s.id 
union all 
select s.id, s.project, 
     null as approved_step, null as approved_emp, 
     na.statusstep as not_approved_step, na.emp as not_approved_emp 
from supertable s 
join notapprovedtable na on na.id = s.id