i have 3 tables
teams
id name
6 WD
7 LGO
10 PS
11 EM
12 SO
13 DM
14 DMS
15 CRO
16 T / T
team_mapping
id team_id parent_team_id
1 6 0
2 7 0
3 10 7
4 11 7
5 12 0
6 13 12
7 14 12
8 15 0
9 16 15
employees
id name team_id
3 Bk 6
4 Nr 6
5 SV 7
6 GK 10
7 JPD 13
8 BSY 16
9 MK 16
10 Ps 16
11 Bji 16
my query is
SELECT t.*
FROM teams t
INNER JOIN employees e ON t.id = e.team_id
INNER JOIN team_mapping tm ON t.id = tm.team_id
WHERE tm.parent_team_id = '0'
GROUP BY t.id
it display like
id name
6 WD
7 LGO
but i want to display parent team name only if atleast have one employee in the either parent team or child team. ie output like
team_id name
6 WD
7 LGO
12 SO
15 CRO
Please help me to write a query and output should be display as above result
Thanks
here is what your query should look like
SELECT DISTINCT CAST(t.id AS INT) id,t.name
FROM teams t
LEFT JOIN employees e ON t.id = e.team_id
LEFT JOIN team_mapping tm ON t.id = tm.team_id
WHERE tm.parent_team_id = '0'
You can use LEFT JOIN for your query.
Then I removed the GROUP BY clause and replace it with DISTICNT.
I just use CAST(t.id AS INT) id so the result will be arranged, either way you can just remove the cast part, the only difference is the arrangement of the result.
select distinct id, name
from teams t inner join
(SELECT case when tm.parent_team_id = 0 then tm.team_id else tm.parent_team_id end as TID
FROM teams t
INNER JOIN team_mapping tm ON t.id = tm.team_id
INNER JOIN employees e ON tm.team_id = e.team_id ) as table2
on t.id = table2.TID
This code is in SQL Server
Here sub query returns duplicate values, and we can't use distinct with the case statement. To avoid duplicate values and to get the parent team name inner join with sub query is needed