Skip to content
Advertisement

Getting null data for left join on same table twice

I have some tables like this:

Employee:

Teams:

TeamMember:

I am trying to get list of employees with info regarding who they are : Team Member or Team Leader or Individual.

Current output:

Expected output:

Query:

Only issue here is that I am getting TeamId and TeamName as blank for “Team Leader” data as shown below:

Current output:

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.

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.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement