CODEWITHSUNDEEP
sqldatabasepostgresql
Anthony
Anthony

Reputation: 15967

Find where two conditions are present in group

I have a table:

ref | name
===========
123abc | received
123abc | pending
134b   | pending
156c   | received

I want to be able to identify instances where a ref only has a pending and not a received. Note there could be multiple receives and pendings for the same ref.

How can I output the ref's that only have a pending and not a received?

So in my example, it would return:

134b | pending

I think it's something like:

SELECT ref, name FROM my_table
WHERE ref IS NOT NULL
GROUP BY ref, name
HAVING ref = 'pending' AND ref = 'received'
;

Upvotes: 0

Views: 44

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I would use aggregation:

select name
from my_table
where ref in ('pending', 'received')
group by name
having min(ref) = 'pending' and min(ref) = max(ref);

The second condition comparing min and max is, strictly speaking, not necessary. But it eliminates the dependence on the alphabetical ordering of the values.

Upvotes: 1

Dimitar Spasovski
Dimitar Spasovski

Reputation: 2132

Another way of doing it is with a WITH statement. This way, there is no need for nested sub-queries.

WITH ref_recieved_pending AS (
    SELECT
      ref,
      sum(CASE WHEN name = 'received'
        THEN 1
          ELSE 0 END) as recieved_count,
      sum(CASE WHEN name = 'pending'
        THEN 1
          ELSE 0 END) as pending_count
    FROM test_table_2
    GROUP BY ref
)
SELECT DISTINCT
  ref,
  'pending' as pending
FROM ref_recieved_pending
WHERE pending_count > 0 AND recieved_count = 0;

Upvotes: 1

Caldazar
Caldazar

Reputation: 3757

You can use not exists for what you need (btw, from your data, column "name" contains values like pending and received):

select distinct ref, name
from my_table t1
where t1.name = 'pending' and not exists (select * from my_table t2 where t1.ref=t2.ref and t2.name='received')

PS. You can validate here with your sample data and my query: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=6fd633fe52129ff3246d8dba55e5fc17

Upvotes: 1

Related Questions