CODEWITHSUNDEEP
sqloracle-database
Ranch Dubois
Ranch Dubois

Reputation: 55

Query to return results only if a value does not exist in any row

Not sure if the title explains this well, but I have a table for logging. So lets say I have the columns Order_ID, Status, and Capture_ID. I am trying to get a list of all Order_ID's that do not contain a Status of Complete in any of the rows that order contains. There can be multiple entries for each Order_ID.

So the rows can look like:

ORDER_ID      STATUS      Capture_ID
1245          COMPLETE    999
1245          ASSIGNED    999
1245          FAILED      999

So I have written a query that returns results that do not contain Complete. It just returns the failed results, the problem is it doesn't check if that ORDER_ID has other rows that have COMPLETE.

Capture_ID is used to track the status of the order. The Order_ID might have 9 other rows to do with other things, but capture_ID gets assigned to this order and has the 3 possible entries of failed, assigned, complete. Cpature_ID will be null for all other rows

Upvotes: 1

Views: 1033

Answers (2)

Marvin Krone
Marvin Krone

Reputation: 1

I think this questions calls for the MINUS command.

Select distinct(order_id)
  from {table_name}
MINUS
Select distinct(order_id)
  from (table_name}
where status = 'Complete'

You can take the output from this SQL and use it for further queries, as needed.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you only want orders in the table, you can use group by and having:

select order_id
from t
group by order_id
having sum(case when status = 'COMPLETE' then 1 else 0 end) = 0;

If you want to run this for all orders, then I'll assume you have an orders table. Use not exists:

select o.*
from orders o
where not exists (select 1
                  from orderstatuses os
                  where os.order_id = o.order_id and
                        os.status = 'COMPLETE'
                 );

Upvotes: 1

Related Questions