SQLQUERY PROBLEM

Question

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

Answer 1

select employeeid
from EmpDept
where DeptId in (2,3) 
group by employeeid
having count(*) = 2

or

select employeeid
from EmpDept
where DeptId in (1,2,3) 
group by employeeid
having count(*) = 3

So, the count must match the number of DeptIds you are checking for. These queries assume you want to specify the DeptIds, which is what I gathered from your question.

Answer 2

this query will find all employees that work for more than 1 department.

select employeeid, count(*) 
from dbo.EmpDept 
group by employeeid 
having count(*) > 1;

if you are hoping to gather data on the employees that cross between a set of specific EmpDepts, you can utilize self joins:

select a.employeeid 
from dbo.EmpDept a, dbo.EmpDept b
where a.employeeid = b.employeeid 
  and a.deptid = 1
  and b.deptid = 2;

using this method, you will have to add another join for every new department you are looking for.

Answer 3

This should do it:

select EmployeeId, count(*) from EmpDept group by EmployeeId having count(*) > 1;