Select distinct users who do not belong to a particular department-CodePudding

Find the list of distinct employees who do not belongs to department X
I want a list of employees whose department value is blank or Y or Z.

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

Inserting some records:

insert into employe values('A101'),('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    
A101    X
A101    Y
A102    Z
A102    X
A103    Z
A103    Y
A104    X
A104    Y
A105    Z
A106    X

department can also be null

Expected Result: If condition is : Distinct Employees who are not in X be:

empID
------
A103
A105

CodePudding user response：

select  distinct empID  
from    (
        select  * 
               ,count(case department when 'x' then 1 end) over (partition by empID) as cnt
        from t
        ) t
where   cnt = 0

empID
A103
A105

Fiddle

CodePudding user response：

You can just use conditional aggregation in a HAVING clause. No joins, window functions or subqueries needed

SELECT t.empID  
FROM t
GROUP BY
    t.empID
HAVING COUNT(CASE WHEN department = 'x' THEN 1 END) = 0;

db<>fiddle

CodePudding user response：

I basically just created a query where I ID'd the empID's that were associated with X, then wrote an outer query performing a NOT IN on those EmployeeID's You have MYSQL and MS-SQL listed so I tried to stay as vanilla as possible to ensure it works for both systems.

SELECT DISTINCT empID
FROM employe
WHERE empID NOT IN
(
    SELECT DISTINCT empID
    FROM employe
    WHERE department = 'X'
)