What is the best way to query this Department-Employee table to get the department which have the exact employees?

user_14156846 0
Viewed 107

I am using MySQL 5.6. I have three tables

Employee(id, ..... PRIMARY KEY (id))
Department(id, ...., PRIMARY KEY (id))
Department_Employee(d_id, e_id,
 FOREIGN KEY(d_ID) REFERENCES Department(id),
 FOREIGN KEY(e_id) REFERENCES Employee(id)
ADD CONSTRAINT PK_D_E_Mapping PRIMARY KEY (e_id, d_id) )

Department and Employee have a many-many relationship.

Let's say I'm given a list of Employee Ids(1, 2, 3) and I need to query the Department_employee table to get the Department_id which has only these 3 employees and no one else.

This is what I've managed to come up with so far.

SELECT id 
  FROM 
     ( SELECT d_id 
         from Department_employee 
        where e_id in (1, 2, 3)
     ) 
 GROUP 
    BY id HAVING COUNT = 3;

I feel like there is definitely a better way to do this.

How can this query be improved?

many-to-many query-optimization mysql
1 Answers

You can group by department id and use GROUP_CONCAT() to set the condition in the HAVING clause:

SELECT d_id 
FROM Department_employee
GROUP BY d_id
HAVING GROUP_CONCAT(e_id ORDER BY e_id) = '1,2,3' -- the ids in ascending order

Or:

SELECT d_id 
FROM Department_employee
GROUP BY d_id
HAVING COUNT(*) = 3 AND SUM(e_id NOT IN (1, 2, 3)) = 0
user_104988280
Related