I have following sample data from a table
Id Dsc
500001 INSURED
500001 THIRD PARTY
500001 THIRD PARTY
500001 THIRD PARTY
500002 INSURED
500002 THIRD PARTY
500003 INSURED
500004 BROKER
500005 CLAIMANT
I wish to extract those Ids for which Dsc is neither 'Insured' nor 'Broker'. Since both columns have repetitive data, I have devised following query for this..
Select Id from table1 where Dsc not in ('Insured', 'Broker')
Except
Select Id from table1 where Dsc in ('Insured', 'Broker')
Is there any alternate way to do this?
SELECT id FROM table1 GROUP BY id
HAVING SUM(CASE WHEN Dsc='Insured' THEN 1 ELSE 0 END)=0
AND SUM(CASE WHEN Dsc='Broker' THEN 1 ELSE 0 END)=0