A table in an Oracle database has columns PRODUCER, CONSUMER, STATUS and DATE. The value of STATUS can be either COMPLETE or FAILED

I am looking for a query which would find all the instances of PRODUCER and CONSUMER pairs where the STATUS is always FAILED

If for example the table contains

I would want the query to return PRODUCER AAA and CONSUMER YYY, because their STATUS is always FAILED

CodePudding user response：

Or

select distinct producer, consumer 
from tab t
where status = 'FAILED' and not exists(
    select 1 from tab t1 where t.producer = t1.producer and t.consumer = t1.consumer and t1.status = 'COMPLETE'
);

Or

select producer, consumer
from tab
group by producer, consumer having( count(1) = count(case when status = 'FAILED' then 1 end));

Etc.

CodePudding user response：

I think using set theory might be an easier solution to this problem -

SELECT PRODUCER, CONSUMER
  FROM your_table
 WHERE STATUS = 'FAILED'
 MINUS
SELECT PRODUCER, CONSUMER
  FROM your_table
 WHERE STATUS <> 'FAILED';