CODEWITHSUNDEEP
sqlsql-servert-sqlsubquerycase
Kaushik49
Kaushik49

Reputation: 21

selecting rows without using 'not in' clause

I have a table like this:

+----+----------+
| ID |    WA    |
+----+----------+
|124 | benefit  |
|124 | ordane   |
|124 | military |
|124 | para     |
|125 | benefit  |
|126 | benefit  |
|126 | ordane   |
|127 | benefit  |
|127 | ordane   |
|127 | military |
|128 | benefit  |
+----+----------+

I wanted those ID's with WA = 'benefit' and exclude those ID's with WA = 'ordane'. I want to avoid not in, not exists and except. I tried with CASE statement but didn't get it. Is there any other way? Thanks!

Upvotes: 1

Views: 52

Answers (1)

Mihai
Mihai

Reputation: 26804

Just use post aggregation filtering with HAVING

SELECT id FROM t GROUP BY id 
HAVING SUM(CASE WHEN WA='benefit' THEN 1 ELSE 0 END)>0
AND SUM(CASE WHEN WA='ordane' THEN 1 ELSE 0 END)=0

Upvotes: 3

Related Questions