Toad World® Forums

Finding records without a corresponding record in same table


#1

I have a table that tracks ordering activity. The initial order entry into the table has the status field set to 15 signifying Active. Once the order is complete, a second entry for the order is added into the same table with the status field set to 3 signifying Complete. I am trying to write a query that will select all entries with a status of 15 but do have any corresponding orders that have a status of 3.

This is a vendor controlled database and therefore unable to make any changes to it’s design.

Any suggestions?

a TOAD newbie…

Thanks


#2

Something like this?

select a.* from orders a
where a.status = 15
and not exists (select null from orders b where a.order_id = b.order_id and b.status = 3) ;