SQL Server：连接4-5个具有不同结构的表

I have three tables

我有三张桌子

Employee
雇员
ApprovalMatrix
ApprovalMatrix
ExpenseMatrix
ExpenseMatrix

Employee contains:

员工包含：

ID  CompanyId  Name
---------------------------------------
9   1          ABC  
10  1          XYZ
11  1          WEW

ApprovalMatrix contains:

ApprovalMatrix包含：

ID      Module  Employeeid   EmployeeLeaderId
--------------------------------------------
11      M1         9            11 
12      M1         10           11 
13      M2         9            11 
55      M3         10           11 
56      M2         10           11

ExpenseMatrix contains:

ExpenseMatrix包含：

ID   Employeeid   EmployeeLeaderId
--------------------------------------------
11      10           9
12      11           9

Expected result:

预期结果：

Module  EmployeeName   EmployeeLeaderName
--------------------------------------------
M1          ABC            WEW 
M1          XYX            WEW 
M2          ABC            WEW 
M3          XYZ            WEW 
M2          XYZ            WEW 
Expense     XYZ            ABC
Expense     WEW            ABC

Like this I have 4-5 different tables need to merge all tables Employee wise, also need module name for which module leader is assigned.

像这样我有4-5个不同的表需要合并所有表Employee明智，还需要模块名称为哪个模块领导者分配。

Below is the query I have tried so far

以下是我到目前为止所尝试的查询

select 
    C.Module, A.Employeeid, B.Name 
from 
    Employee  B 
join 
    ExpenseMatrix A on A.EmployeeLeaderId = B.Id
join 
    ApprovalMatrix C on C.EmployeeLeaderId = B.Id
where 
    B.EmpStatus = 1 and A.EmployeeId = 56

2 个解决方案

#1

select 
    A.Module, e1.name, e2.Name 
from 
    ApprovalMatrix AM
inner join  Employee  E1
    on AM.EmployeeId = E1.ID
inner join  Employee  E2
    on AM.EmployeeLeaderId = E2.ID
union all
select 'Expense', e1.name, e2.name
From ExpenseMatrix EM
inner join  Employee  E1
    on EM.EmployeeId = E1.ID
inner join  Employee  E2
    on EM.EmployeeLeaderId = E2.ID

#2

Hi try below code .

嗨尝试下面的代码。

;with temp as
(select Module , Employeeid  , EmployeeLeaderId from ApprovalMatrix 
union all 
select 'Expence' as Module  ,Employeeid , EmployeeLeaderId from ExpenseMatrix ) 

select Module, b.Name as  EmployeeName ,c.name as  EmployeeLeaderName  from temp a
left join Employee b on a.EmployeeLeaderId =b.Id
left join Employee c  on a.EmployeeLeaderId =c.id

Note Add more table in union all in CTE block.

注意在CTE块中的union all中添加更多表。

#1

select 
    A.Module, e1.name, e2.Name 
from 
    ApprovalMatrix AM
inner join  Employee  E1
    on AM.EmployeeId = E1.ID
inner join  Employee  E2
    on AM.EmployeeLeaderId = E2.ID
union all
select 'Expense', e1.name, e2.name
From ExpenseMatrix EM
inner join  Employee  E1
    on EM.EmployeeId = E1.ID
inner join  Employee  E2
    on EM.EmployeeLeaderId = E2.ID

#2