I have some tables like this:
Employee:
x
EmployeeId Name DeptId
----------------------------------
100 John 100
101 Kery 100
102 Tiny 100
Teams:
TeamId TeamLeaderId TeamName
------------------------------------------
1 100 Finance-2021
TeamMember:
TeamMemberId TeamId EmployeeId
----------------------------------------
1 1 101
I am trying to get list of employees with info regarding who they are : Team Member or Team Leader or Individual
.
Current output:
TeamId TeamName EmployeeIdentity EmployeeId EmployeeName
------------------------------------------------------------------
null null Team Leader 100 John
1 Finance-2021 Team Member 101 Kery
Expected output:
TeamId TeamName EmployeeIdentity EmployeeId EmployeeName
-----------------------------------------------------------------------
1 Finance-2021 Team Leader 100 John
1 Finance-2021 Team Member 101 Kery
Query:
INSERT INTO #tempMembers (ID)
(SELECT EmployeeId
FROM TeamMembers
WHERE TeamId IN (1) AND IsDeleted = 0
UNION
SELECT TeamLeaderId
FROM TEAMS
WHERE TeamId IN (1))
SELECT
t.TeamId as 'TeamId',
t.TeamName as 'TeamName',
CASE
WHEN tm.EmployeeId IS NOT NULL THEN 'Team Member'
WHEN t1.TeamLeaderId IS NOT NULL THEN 'Team Leader'
ELSE 'Individual'
END AS 'EmployeeIdentity',
e.EmployeeId as 'EmployeeId',
e.EmployeeName as 'EmployeeName',
FROM
Employee AS e
LEFT JOIN
TeamMember tm ON tm.EmployeeId = e.EmployeeId AND tm.IsDeleted = 0
LEFT JOIN
Teams t ON t.TeamId = tm.TeamId
LEFT JOIN
Teams t1 ON t1.TeamLeaderId = e.EmployeeId
WHERE
e.IsDeleted = 0
AND e.EmployeeId IN (SELECT * FROM #tempMembers)
Only issue here is that I am getting TeamId and TeamName
as blank for “Team Leader” data as shown below:
Current output:
TeamId TeamName EmployeeIdentity EmployeeId EmployeeName
null null Team Leader 100 John
Can anyone please help me with this issue?
Advertisement
Answer
This approach does not need a temp table and uses the nulls from the left join to determine whether a member is a Team Member
, Team Leader
or Individual
.
The where condition applies the filter from your initial union in the temp table.
I’ve also included a db-fiddle below to demonstrate.
SELECT
CASE
WHEN tl.TeamLeaderId IS NOT NULL THEN tl.TeamId
WHEN tmt.TeamId IS NOT NULL THEN tmt.TeamId
ELSE NULL
END as TeamId,
CASE
WHEN tl.TeamLeaderId IS NOT NULL THEN tl.TeamName
WHEN tmt.TeamName IS NOT NULL THEN tmt.TeamName
ELSE NULL
END as TeamName,
CASE
WHEN tl.TeamLeaderId IS NOT NULL THEN 'Team Leader'
WHEN tm.EmployeeId IS NOT NULL THEN 'Team Member'
ELSE 'Individual'
END as EmployeeIdentity,
e.EmployeeId,
e.Name as EmployeeName
FROM
Employee e
LEFT JOIN
Teams tl ON e.EmployeeId = tl.TeamLeaderId AND tl.isDeleted=0
LEFT JOIN
TeamMember tm ON e.EmployeeId = tm.EmployeeId AND tm.isDeleted=0
LEFT JOIN
Teams tmt ON tm.TeamId = tmt.TeamId AND tmt.isDeleted=0
WHERE
tl.TeamId = 1 OR tmt.TeamId=1
TeamId | TeamName | EmployeeIdentity | EmployeeId | EmployeeName |
---|---|---|---|---|
1 | Finance-2021 | Team Leader | 100 | John |
1 | Finance-2021 | Team Member | 101 | Kery |
View on DB Fiddle or SQL Server Db Fiddle
Let me know if this works for you.