Suppose I have a junction table
EmployeeId DeptId
--------- ------
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
4 1
5 2
5 3
6 1
6 2
6 3
So 1 employee can work in many departments
My problem is to find which employee works in multiple departments?
e.g.
If I want to search an employee who works for department 1,2,3, the result will be: 1,2,6
If I want to search an employee who works for department 2 & 3 the result will be 1,2,5,6
If I want to search an employee who works for department 1 & 2 the result will be 1,2 ,3,6
I tried with the following queries
a) SELECT DISTINCT EmployeeId FROM dbo.EmpDept WHERE DeptId in (2,3)
I got wrong result
b) SELECT DISTINCT EmployeeId FROM dbo.EmpDept WHERE DeptId = 2 AND DeptId = 3
This time I got no records
Please help me out.
N.B.~ I only simulated my real time project scenario. I cannot reveal the exact schema or table names or anything related to the project as it is confidential.
Thanks in advance