Question 113956 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Select team that consists of members that belong to the same, single department

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!

UPD:

I came to solution:

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 count(*) = 1 
       And avg(I.department_id)=12;

but the accepted one looks cleaner to me (and moreover it is probably more performant)

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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;

    dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search