SQL return only specific rows with specific status

Question

I have this table below with two columns

Order_No     Order_Status
A            Receiving
A            Active
A            Retired
A            Ordering
B            Receiving
B            Ordering 
C            Active
C            Retired
D            Receiving
E            Ordering 

I would like to get Order_no B, D and E records because it's order status is ( Receiving/Ordering ). It should filter out A and C because both have Active and Retired status.

I tried the below query but it's not showing up the results.

select ORDER_NUMBER
from table ror
where ror.use_Status 
Order_Status not in ('Active', 'Retired')
and Order_Status in ('Receiving', 'Ordering').

Could anyone please tell me what wrong I am doing or I'm missing any joins?

Answer 1

You can use group by and having:

select order_no
from mytable
group by order_no
having max(case when status = 'Receiving'            then 1 else 0 end) = 1
   and max(case when status = 'Ordering'             then 1 else 0 end) = 1
   and max(case when status not in ('Receiving', 'Ordering') then 1 else 0 end) = 0

This phrases as: get all orders that have both "Receiving" and "Ordering" statuses, and no other status.

If a given order cannot have the same status twice, then the having can be simplified a little:

having sum(case when status     in ('Receiving', 'Ordering') then 1 else 0 end) = 2
   and sum(case when status not in ('Receiving', 'Ordering') then 1 else 0 end) = 0

---

Edit - if you want order that have either "Receiving" and "Ordering" statuses (not necessarily both), then a single condition is sufficient:

having max(case when status not in ('Receiving', 'Ordering') then 1 else 0 end) = 0