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.

Why don't you want to use `GROUP BY` and `HAVING`? Using `JOIN` may lead to performance problem. — Felix Pamittan, May 07 '15 at 05:04
@wewesthemenace, Because of I want to join this condition with another one by using `AND`. — MAK, May 07 '15 at 05:08
@MAK what about this approach `select empID from ( select empID,string_agg(department,',') dep from employe group by empID)t where dep in ('Z,Y')` ?? — Vivek S., May 07 '15 at 05:11
@vivek, Awesome! But in sql server `'string_agg' is not a recognized built-in function name`. In PostgreSQL works fine. — MAK, May 07 '15 at 05:15
@MAK yup but you tagged PostgreSQL that's why :D, tag only related database — Vivek S., May 07 '15 at 05:19

Felix Pamittan · Accepted Answer · 2015-05-07T05:17:53.807

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
    )

@wewesthemenace, Actually I am preparing a string for where clause a query. In that string I can't use the `group by` because I have many other condition that string. I just want to concatenate this condition with that. — MAK, May 07 '15 at 05:20

score 7 · Answer 2 · edited May 19 '15 at 17:33

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

Nice! For those not blessed to have `CROSS APPLY`, use `GROUP_CONCAT` or similar. — bishop, May 07 '15 at 15:21

SimarjeetSingh Panghlia · Answer 3 · 2015-05-07T05:14:53.703

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

ughai · Answer 4 · 2015-05-07T05:19:58.120

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

score 4 · Answer 5 · answered May 07 '15 at 05:01

4

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') ;

answered May 07 '15 at 05:01

Chetan Bodke

523
6
28

1

There is no row that has two different values in one cell at the same time. Ever. – D_4_ni May 07 '15 at 15:00
`where department = 'Z' and department = 'Y'` is never true – Oct 12 '17 at 11:58

score 4 · Answer 6 · answered May 07 '15 at 05:17

4

for If condition is : Z and Y

   SELECT EMPID FROM EMPLOYE WHERE DEPARTMENT='Z'  AND 
   EMPID IN (SELECT EMPID FROM EMPLOYE WHERE DEPARTMENT ='Y')AND
   EMPID NOT IN(SELECT EMPID FROM EMPLOYE WHERE DEPARTMENT NOT IN ('Z','Y'))

answered May 07 '15 at 05:17

Pu297

151
8

score 3 · Answer 7 · answered May 07 '15 at 05:01

The following query works when you want employees from departments 'Y' and 'Z' and not 'X'.

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

For your second case, simply replace not in with in in the last condition.

score 3 · Answer 8 · answered May 07 '15 at 05:12

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.

score 1 · Answer 9 · answered May 07 '15 at 13:08

You can too use GROUP BY and HAVING — you just need to do it in a subquery.

For example, let's start with a simple query to find all employees in departments X and Y (and not in any other departments):

SELECT empID,
  GROUP_CONCAT(DISTINCT department ORDER BY department ASC) AS depts
FROM emp_dept GROUP BY empID
HAVING depts = 'X,Y'

I've used MySQL's GROUP_CONCAT() function as a convenient shortcut here, but you could get the same results without it, too, e.g. like this:

SELECT empID,
  COUNT(DISTINCT department) AS all_depts,
  COUNT(DISTINCT CASE
    WHEN department IN ('X', 'Y') THEN department ELSE NULL
  END) AS wanted_depts
FROM emp_dept GROUP BY empID
HAVING all_depts = wanted_depts AND wanted_depts = 2

Now, to combine this with other query condition, simply take a query that includes the other conditions, and join your employees table against the output of the query above:

SELECT empID, name, depts
FROM employees
JOIN (
    SELECT empID,
      GROUP_CONCAT(DISTINCT department ORDER BY department ASC) AS depts
    FROM emp_dept GROUP BY empID
    HAVING depts = 'X,Y'
  ) AS tmp USING (empID)
WHERE -- ...add other conditions here...

Here's an SQLFiddle demonstrating this query.

Ps. The reason why you should use a JOIN instead of an IN subquery for this is because MySQL is not so good at optimizing IN subqueries.

Specifically (as of v5.7, at least), MySQL always converts IN subqueries into dependent subqueries, so that the subquery must be re-executed for every row of the outer query, even if the original subquery was independent. For example, the following query (from the documentation linked above):

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

gets effectively converted into:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

This may still be reasonably fast, if t2 is small and/or has an index allowing fast lookups. However, if (like in the original example above) executing the subquery might take a lot of work, the performance can suffer badly. Using a JOIN instead allows the subquery to only be executed once, and thus typically offers much better performance.

score 1 · Answer 10 · answered May 07 '15 at 16:22

1

What about a self join? (ANSI Compliant - worked for 20+ years)

SELECT * FROM employee e JOIN employee e2 ON e.empid = e2.empid
WHERE e.department = 'x' AND e2.department ='y'

This shows that a101 and a104 both work in both departments.

answered May 07 '15 at 16:22

roblem

527
5
8

potashin · Answer 11 · 2015-05-08T22:57:54.567

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

score 1 · Answer 12 · 2017-10-12T12:27:51.520

In Postgres this can be simplified using arrays:

select empid
from employee
group by empid
having array_agg(department order by department)::text[] = array['Y','Z'];

It's important to sort the elements in the array_agg() and compare them to a sorted list of departments in the same order. Otherwise this won't return correct answers.

E.g. array_agg(department) = array['Z', 'Y'] might potentially return wrong results.

This can be done in a more flexible manner using a CTE to supply the departments:

with depts_to_check (dept) as (
   values ('Z'), ('Y')
)
select empid
from employee
group by empid
having array_agg(department order by department) = array(select dept from depts_to_check order by dept);

That way the sorting of the elements is always done by the database and will be consistent between the values in the aggregated array and the one to which it is compared.

An option with standard SQL is to check if at least one row has a different department together with counting all rows

select empid
from employee
group by empid
having min(case when department in ('Y','Z') then 1 else 0 end) = 1
  and count(case when department in ('Y','Z') then 1 end) = 2;

The above solution won't work if it's possible that a single employee is assigned twice to the same department!

The having min (...) can be simplified in Postgres using the aggregate bool_and().

When applying the standard filter() condition to do conditional aggregation this can also be made to work with situation where an employee can be assigned to the same department twice

select empid
from employee
group by empid
having bool_and(department in ('Y','Z'))
  and count(distinct department) filter (where department in ('Y','Z')) = 2;

bool_and(department in ('Y','Z')) only returns true if the condition is true for all rows in the group.

Another solution with standard SQL is to use the intersection between those employees that have at least those two departments and those that are assigned to exactly two departments:

-- employees with at least those two departments
select empid
from employee
where department in name in ('Y','Z')
group by empid
having count(distinct department) = 2

intersect

-- employees with exactly two departments
select empid
from employee
group by empid
having count(distinct department) = 2;

Select users belonging only to particular departments

12 Answers12

XML Solution with CROSS APPLY

Linked