SQL select groups where values are exclusively in a list-CodePudding

Given a table with two columns, department and employee, where every employee belongs to 1 department.

Given a list of employee ids, how do I select departments where all employees are in the list?

Department	Employee
finance	1
finance	2
marketing	3
marketing	4
IT	5
IT	6

given (2,3,4,5,6), returns ('marketing', 'IT')

(Note: DB flavor does not matter to me, you may use standard or DB-specific SQL)

CodePudding user response：

Put your input id's into a table, join to the existing table, and check counts between input and the existing employees. If they match, output.

Change this to whatever RDBMS you're using

CREATE TEMP TABLE input_vals (id int); 
insert into input_vals 
values 
(2), 
(3), 
(4), 
(5), 
(6); 

with cte_joins AS (
    select ot.department, 
    count(ot.employee) AS employee_count1, 
    sum(case when iv.id is not null then 1 else 0 end) AS employee_count2

    from orig_table ot
    left join input_vals iv
        on ot.employee = iv.id

    group by ot.department
)
select department 
from cte_joins
where employee_count1 = employee_count2

CodePudding user response：

You can use a couple of derived tables - one to get the count of employees in each department and one to get the count of employees in each department limited by your list of employees. Return the ones that match.

select
distinct full_list.department
from (
  select
  department,
  count (*) as cnt
  from
  <your table>
  group by department) full_list
  inner join (
  select
  department,
count (*) as cnt
from
<your table>
group by department
where
employee in (2,3,4,5,6)
 ) limited_list
on full_list.department = limited_list.department
and full_list.cnt = limited_list.cnt

CodePudding user response：

Assume you employee list is captured as a column in a table. Since you didn't provide any detail on the tool set you are using.

Something like this is a common pattern, depending on your dbms there's other more simple syntax.

select distinct
       de.department
  from department_employee de
 where not exists
         ( select 1
             from department_employee de2
            where not exists
                    ( select 1
                        from employee_list el
                       where de2.employee = el.employee
                    )
              and de.department = de2.department
         );

edit: Not quite sure why my answer was downvoted. I deleted it just in case to test further and it's looking fine to me: http://sqlfiddle.com/#!17/b56b2/2

Would appreciate a comment explaining the downvote if I'm steering OP in the wrong direction.

CodePudding user response：

-- departments
select department from t where employee in (2,3,4,5,6);

-- departments containing extra persons
select department from t 
where department in (select department from t where employee in (2,3,4,5,6))
      and employee not in (2,3,4,5,6);

--- departments, employees from the list, with no extra
select department from t 
where employee in (2,3,4,5,6) and department not in (
    select department from t 
    where department in (
        select department from t where employee in (2,3,4,5,6)
    ) and employee not in (2,3,4,5,6)
)