CODEWITHSUNDEEP
sqldatabaseselectdistinct
priyanka.sarkar
priyanka.sarkar

Reputation: 26498

Employees and departments database query with Sql

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

Upvotes: 0

Views: 4436

Answers (3)

akf
akf

Reputation: 39485

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.

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

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.

Upvotes: 2

Karl T.
Karl T.

Reputation: 502

This should do it:

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

Upvotes: 2

Related Questions