CODEWITHSUNDEEP
sql-server
nc sql
nc sql

Reputation: 3

SQL Server : condition to check the Employees who does not have a particular role and do not have another role

I have 2 tables dbo.[Employee] and dbo.[EmployeeRole], I need to get all the [EmployeeId] from dbo.[Employee] table whose [EmployeeTypeId] = 1 and the condition is these EmployeeId's must also be present in the dbo.[EmployeeRole] table with only RoleId = 5 AND they should not have RoleId = 6.

In short the EmployeeId's which I am going to fetch from the dbo.[Employee] table should have a record with RoleId = 5 in the dbo.[EmployeeRole] table and they should not have an entry with RoleId = 6.

Note: each EmployeeId can have multiple roles in dbo.[EmployeeRole] table

SELECT [EmployeeId] 
FROM dbo.[Employee] 
WHERE [EmployeeTypeId] = 1 
  AND EmployeeId IN (SELECT [EmployeeId] 
                     FROM dbo.[EmployeeRole] 
                     WHERE [EmployeeRoleId] = 5)

I could not add a condition where I need to check that the user does not have a record with [RoleId] = 6

Upvotes: 0

Views: 378

Answers (4)

Annamalai D
Annamalai D

Reputation: 899

You can first select EmployeeIds with EmployeeRoleId =5 and use NOT EXISTS to filter out the EmployeeIds who also hasEmployeeRoleId =6

Updated the Query.

SELECT [EmployeeId] 
FROM dbo.[Employee] E 
WHERE [EmployeeTypeId] = 1 
AND [EmployeeRoleId]  IN (SELECT [EmployeeId] 
 FROM dbo.[EmployeeRole]
 WHERE  [EmployeeRoleId] = 5)
AND NOT EXISTS
( 
 SELECT [EmployeeId] 
 FROM dbo.[EmployeeRole] ER 
 WHERE ER.[EmployeeId] = E.[EmployeeId]
 AND [EmployeeRoleId] = 6
 )

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

SELECT e.EmployeeId
FROM dbo.Employee as e INNER JOIN dbo.EmployeeRole as er
    ON er.EmployeeId = e.EmployeeId
WHERE e.EmployeeTypeId = 1 
GROUP BY e.EmployeeId
HAVING COUNT(CASE WHEN er.EmployeeRoleId = 5 THEN 1 END) = 1
   AND COUNT(CASE WHEN er.EmployeeRoleId = 6 THEN 1 END) = 0

Upvotes: 0

Elbaraa
Elbaraa

Reputation: 3

It's not that complicated, You can first select EmployeeIds with EmployeeRoleId =5 and select the hasEmployeeRoleId who they are different than 6 using "not exist"

select [EmployeeId] from dbo.[Employee] as emp
 where [EmployeeTypeId] = 1 and [EmployeeRoleId] = 5
and not exist( select[EmployeeId] from dbo.[EmployeeRole] as emprole 
and emprole.[EmployeeId] = emp.[EmployeeId]
 and [EmployeeRoleId] = 6);
go

Upvotes: 0

Stu
Stu

Reputation: 32609

Several permutations for this exist, coincidentally exists is one method;

Select EmployeeId
from Employees e 
where exists (select * from EmployeeRole r where r.EmployeeId=e.EmployeeId and r.RoleId=5)
and not exists (select * from EmployeeRole r where r.EmployeeId=e.EmployeeId and r.RoleId=6)
where e.EmployeeTypeId=1

Upvotes: 1

Related Questions