sqlsql-serversql-except

Filter a group in mysql


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?


Solution

  • 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