Select users belonging only to particular departme

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

try this

select empID from employe 
where empId in (select empId from employe 
where department = 'Z' and department = 'Y') 
and empId not in (select empId from employe 
where department = 'X') ;

Answer 2

Solution using where clause:

select distinct e.empID
from employe e
where exists( select * 
              from employe
              where empID = e.empID
              having count(department) = count(case when department in('Y','X','Z') then department end)
                 and count(distinct department) = 3)

exists checks if there are records for specific EmpId that have total count of departments equal to conditional count of only matching departments and that it is also equal to the number of departments provided to the in clause. Also worth mentioning that here we apply having clause without the group by clause, on the whole set, but with already specified, only one empID.

SQLFiddle

You can achieve this without the correlated subquery, but with the group by clause:

select e.empId
from employe e
group by e.empID
having count(department) = count(case when department in('Y','X','Z') then department end)
   and count(distinct department) = 3

SQLFiddle

You can also use another variation of having clause for the query above:

having count(case when department not in('Y','X', 'Z') then department end) = 0
   and count(distinct case when department in('Y','X','Z') then department end) = 3

SQLFiddle

Answer 3

For condition 1:z and y

 select z.empID from (select empID from employe where department = 'z' ) as z
inner join (select empID from employe where department = 'y' )  as y 
on z.empID = y.empID
where z.empID Not in(select empID from employe where department = 'x' ) 

For condition 1:z and x

select z.empID from (select empID from employe where department = 'z' ) as z
inner join (select empID from employe where department = 'x' )  as x 
on z.empID = x.empID
where z.empID Not in(select empID from employe where department = 'y' )

For condition 1:z,y and x

select z.empID from (select empID from employe where department = 'z' ) as z
inner join (select empID from employe where department = 'x' )  as x 
on z.empID = x.empID
inner join (select empID from employe where department = 'y' )  as y on 
y.empID=Z.empID

Answer 4

Try this,

SELECT  a.empId
FROM    employe a
        INNER JOIN
        (
            SELECT  empId
            FROM    employe 
            WHERE   department IN ('X', 'Y', 'Z')
            GROUP   BY empId
            HAVING  COUNT(*) = 3
           )b ON a.empId = b.empId
GROUP BY a.empId

Count must based on number of conditions.

Answer 5

You can use GROUP BY with having like this. SQL Fiddle

SELECT empID 
FROM employe
GROUP BY empID
HAVING SUM(CASE WHEN department= 'Y' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN department= 'Z' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN department NOT IN('Y','Z') THEN 1 ELSE 0 END) = 0

Without GROUP BY and Having

SELECT empID 
FROM employe E1
WHERE (SELECT COUNT(DISTINCT department) FROM employe E2 WHERE E2.empid = E1.empid and  department IN ('Z','Y')) = 2
EXCEPT
SELECT empID 
FROM employe
WHERE department NOT IN ('Z','Y')

If you want to use any of the above query with other tables using a join you can use CTE or a derived table like this.

;WITH CTE AS 
(

    SELECT empID 
    FROM employe
    GROUP BY empID
    HAVING SUM(CASE WHEN department= 'Y' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN department= 'Z' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN department NOT IN('Y','Z') THEN 1 ELSE 0 END) = 0
)
SELECT cols from CTE join othertable on col_cte = col_othertable

Answer 6

I know that this question has already been answered, but it was a fun problem to do and I tried to do it in a way that no one else has. Benefits of mine is that you can input any list of strings as long as each value has a comma afterwards and you don't have to worry about checking counts.

Note: Values must be listed in alphabetic order.

XML Solution with CROSS APPLY

select DISTINCT empID
FROM employe A
CROSS APPLY
            (
                SELECT department + ','
                FROM employe B
                WHERE A.empID = B.empID
                ORDER BY department
                FOR XML PATH ('')
            ) CA(Deps)
WHERE deps = 'Y,Z,'

Results:

empID
----------
A103