Filter a group in mysql

Question

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?

score 6 · Accepted Answer · answered Jul 06 '15 at 06:21

6

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

answered Jul 06 '15 at 06:21

Mihai

26,325
7
66
81

score 1 · Answer 2 · answered Jul 06 '15 at 06:31

1

You can write a query as:

SELECT Id FROM (
SELECT Id , SUM(CASE WHEN Dsc IN ('INSURED','BROKER') THEN 1 ELSE 0 END ) AS Condition
FROM @Test
GROUP BY Id
) T 
WHERE Condition = 0

DEMO

answered Jul 06 '15 at 06:31

Deepshikha

9,896
2
21
21

score 0 · Answer 3 · answered Jul 06 '15 at 06:22

0

Use subquery for the same:

Select id,dsc from (select id,dsc from table1 group by id, dsc) 
where dsc not in('Insured', 'Broker')

answered Jul 06 '15 at 06:22

Nikhil Batra

3,118
14
19

score -1 · Answer 4 · edited Jul 06 '15 at 08:07

-1

Use this code to stop repeating column

Select distinct *from table1

edited Jul 06 '15 at 08:07

MathieuF

3,130
5
31
34

answered Jul 06 '15 at 06:19

Laxman Parmar

120
9

Filter a group in mysql

4 Answers4