How do I get sales orders where every line of the sales order is closed?

Question

The table has 'Sales_Order_ID', 'Sales_Order_Line_Number', and 'Sales_Order_Line_staus' among other fields. I want to retrieve 'Sales_Order_ID' where each record for that 'Sales_Order_ID' has the same 'Sales_Order_Line_Status'.

So, if every record for sales order X has status of 'closed', then I want to retrieve it. If sales order Y has three records with status 'closed' and one record with status 'open', then I don't want to retrieve it.

I tried:

SELECT DISTINCT s1.so_ID, s1.SO_line_status
FROM sales_order_table s1
INNER JOIN sales_order_table s2
ON s1.so_id = s2.so_id
  AND s1.so_line_status = s2.so_line_status
ORDER BY s1.so_id 

To no success. The following seems to give the opposite of what I want:

SELECT DISTINCT s1.so_ID, s1.SO_line_status
FROM sales_order_table s1
INNER JOIN sales_order_table s2
ON s1.so_id = s2.so_id
  AND s1.so_line_status <> s2.so_line_status
ORDER BY s1.so_id  

So I tried:

SELECT DISTINCT s1.so_ID, s1.SO_line_status
FROM sales_order_table s1
INNER JOIN sales_order_table s2
ON s1.so_id = s2.so_id
  AND NOT s1.so_line_status <> s2.so_line_status
ORDER BY s1.so_id

To no success.

Then I went totally noob and changed the join type around just hoping that it would work. Am I close here or totally going about it the wrong way?

Also, I realize that the queries above do not restrict the results to 'closed' status, but I figured if I could get one that returns only all same status lines, I could then restrict them to 'closed'.

Sorry if this is unclear! If so, I will try to clarify.

Answer 1

SELECT so_ID
FROM sales_order_table 
GROUP BY so_ID
HAVING MAX(SO_line_status) = 'Closed' AND
       MIN(SO_line_status) = 'Closed' AND
       COUNT(CASE WHEN SO_line_status IS NULL THEN 1 END) = 0

You could also use EXCEPT if your RDBMS supports it

SELECT so_ID
FROM sales_order_table 
WHERE SO_line_status = 'Closed'
EXCEPT
SELECT so_ID
FROM sales_order_table 
WHERE SO_line_status IS NULL OR SO_line_status <> 'Closed'