CODEWITHSUNDEEP
sqlpostgresql
Lucas Dresl
Lucas Dresl

Reputation: 1170

If one condition is met by group by, filter it

I am trying to filter from the following table all the ids which have at least one status = C. If it has C status filter it from my existing table

This is an example of my hole dataset (example to illustrate my problem)

    id  | status
 -------------
   4567 | B
   4567 | A
     27 | A
     27 | A
     27 | C
      9 | C
      9 | B

Expected result

    id  | status
 -------------
   4567 | B
   4567 | A

Upvotes: 0

Views: 154

Answers (2)

Tekin Güllü
Tekin Güllü

Reputation: 363

Try this

SELECT id,status FROM TABLE T 
   WHERE id NOT IN (SELECT id FROM TABLE T1 WHERE status ='C' )

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Use not exists:

select t.*
from t
where not exists (select 1 from t t2 where t2.id = t.id and t2.status = 'C');

Upvotes: 0

Related Questions