How to select ID in only 1 criteria-CodePudding

In the dataset shown, I am looking to isolate or show the ID that is only in workbaskets a,b or c but not in d,e or f. I would expect the ID of 111222 to fit that criteria. I have tried this SQL with no luck. Any suggestions?

select id
from table
where workbasket in ('a','b','c') and workbasket not in ('d','e','f')

Workbasket  ID
a           111222
a           123456
b           987654 
c           112333
d           123456 
e           987654
f           112333

CodePudding user response：

select id
from table
where workbasket in ('a','b','c')

EXCEPT

select id
from table
where workbasket in ('d','e','f')

CodePudding user response：

You can also use NOT EXISTS subquery

select id
from table t1
where workbasket in ('a','b','c') 
and NOT EXISTS 
(
 select *
 from table t2
 where t1.id = t2.id
 AND t2.workbasket in ('d','e','f') 
)

CodePudding user response：

SQL is by default case-insensitive, but it's possible your database has that flag changed; you also don't need to specify a NOT IN clause here because anything outside of ('a', 'b', 'c') is already ignored by your first clause. TABLE is a SQL term, and may also be causing some kind of problem with your query; alias it if you can - if you can't, wrap it in backticks.

Try this:

SELECT ID
FROM `table`
WHERE Workbasket IN ('a', 'b', 'c');

CodePudding user response：

WITH CTE(Workbasket, ID) AS
(
   SELECT 'A',123456 UNION ALL
   SELECT 'A',111222 UNION ALL
   SELECT 'B',987654 UNION ALL
   SELECT 'C',112333 UNION ALL
   SELECT 'D',123456 UNION ALL
   SELECT 'E',987654 UNION ALL
   SELECT 'F',112333
)
SELECT C.ID
FROM CTE AS C
GROUP BY C.ID
HAVING MAX(C.Workbasket) IN('A','B','C')