Solved - SQL Server 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：

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