I'm trying to make an sql request that turned out surprisingly difficult to me (or maybe this is just the end of the day, sigh).
I have 3 tables:
Team:
| id |
|---|
| 1 |
| 2 |
| 3 |
Team member table:
| id | team_id |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
| 8 | 2 |
| 9 | 3 |
| 10 | 3 |
| 11 | 3 |
| 12 | 3 |
Team member info table:
| id | team_member_id | department_id |
|---|---|---|
| 1 | 1 | 12 |
| 3 | 2 | 43 |
| 5 | 3 | 23 |
| 7 | 4 | 12 |
| 9 | 5 | 12 |
| 11 | 6 | 12 |
| 13 | 7 | 12 |
| 15 | 8 | 12 |
| 17 | 9 | 43 |
| 19 | 10 | 23 |
| 21 | 11 | 14 |
| 23 | 12 | 23 |
These tables are simplified, so don't pay much attention to its structure.
What I need to do is to find ids of teams which consists of members that belong to SINGLE department_id and this department id should be a parameter.
So in our example I need to find teams, which members belong to department 12.
This is team(id=2) since it consists of members id=5,6,7,8 and all of them belong to department 12. Team 1 and Team 3 doesn't suit our needs since its members belong to multiple departments: (12, 43, 23) and (43, 23, 14) respectively.
Thanks a lot!
CodePudding user response：
this query selects the teams whose members belong all to department_id = 12 :
SELECT tm.team_id
FROM Team_member_info tmi
INNER JOIN Team_member tm
ON tm.id = tmi.team_member_id
GROUP BY tm.team_id
HAVING bool_and(tmi.department_id = 12)
see dbfiddle
CodePudding user response：
This could also be done with other aggregate functions max and min.
Select M.team_id
From Team_Member As M Inner Join Team_Member_Info As I On (M.id=I.team_member_id)
Group by M.team_id
Having Max(I.department_id)=Min(I.department_id)
And Max(I.department_id)=12;