Select users belonging only to particular departments

Question

I have the following table with two fields namely a and b as shown below:

create table employe (     empID varchar(10),     department varchar(10) ); 

Inserting some records:

insert into employe values('A101','Z'),('A101','X'),('A101','Y'),('A102','Z'),('A102','X'),              ('A103','Z'),('A103','Y'),('A104','X'),('A104','Y'),('A105','Z'),('A106','X');   select * from employe; 

empID   department ------------------ A101    Z A101    X A101    Y A102    Z A102    X A103    Z A103    Y A104    X A104    Y A105    Z A106    X 

Note: Now I want to show the employee who is only and only belongs to the department Z and Y. So according to the condition the only employee A103 should be displayed because of he only belongs to the department Z and Y. But employee A101 should not appear because he belong to Z,X, and Y.

Expected Result:

If condition is : Z and Y then result should be:

empID ------ A103 

If condition is : Z and X then result should be:

empID ------ A102 

If condition is : Z,X and Y then result should be:

empID ------ A101 

Note: I want to do it in the where clause only (don't want to use the group by and having clauses), because I'm going to include this one in the other where also.

Answer 1

This is a Relational Division with no Remainder (RDNR) problem. See this article by Dwain Camps that provides many solution to this kind of problem.

First Solution

SQL Fiddle

SELECT empId FROM (     SELECT         empID, cc = COUNT(DISTINCT department)     FROM employe     WHERE department IN('Y', 'Z')     GROUP BY empID )t WHERE     t.cc = 2     AND t.cc = (         SELECT COUNT(*)         FROM employe         WHERE empID = t.empID     ) 

Second Solution

SQL Fiddle

SELECT e.empId FROM employe e WHERE e.department IN('Y', 'Z') GROUP BY e.empID HAVING     COUNT(e.department) = 2     AND COUNT(e.department) = (SELECT COUNT(*) FROM employe WHERE empID = e.empId) 

---

Without using GROUP BY and HAVING:

SELECT DISTINCT e.empID FROM employe e WHERE     EXISTS(         SELECT 1 FROM employe WHERE department = 'Z' AND empID = e.empID     )     AND EXISTS(              SELECT 1 FROM employe WHERE department = 'Y' AND empID = e.empID     )     AND NOT EXISTS(         SELECT 1 FROM employe WHERE department NOT IN('Y', 'Z') AND empID = e.empID     )