How to receive sales orders when each line of a 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 get "Sales_Order_ID", where each record for this "Sales_Order_ID" has the same "Sales_Order_Line_Status".

So, if every record for sales order X has a status of “closed”, I want to receive it. If in order Y there are three records with the status “closed” and one record with the status “open”, I do not 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 

Without 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 

Without success.

Then I went completely noob and changed the type of connection, just hoping it worked. Am I closing here or totally cheating this wrong?

In addition, I understand that the above queries do not limit the results to a “closed” status, but I decided that if I could get one that returns only all the same status lines, I could then limit them to “closed”.

Sorry if this is unclear! If so, I’ll try to explain.

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 can 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' 

Answer 2

The main approach here is to group by ID and status. If the calculation of this grouping is equal to the account using only the identifier, then you will know that all lines have the same status.

 SELECT s1.so_ID, s1.SO_line_status FROM sales_order_table s1 GROUP BY s1.so_ID, s1.SO_Line_status HAVING COUNT(*) = (SELECT COUNT(*) FROM sales_order_table s2 WHERE s2.so_ID = s1.so_ID) 

To narrow it down to closed, just add a WHERE clause:

 SELECT s1.so_ID, s1.SO_line_status FROM sales_order_table s1 WHERE s1.SO_line_status = 'closed' GROUP BY s1.so_ID, s1.SO_Line_status HAVING COUNT(*) = (SELECT COUNT(*) FROM sales_order_table s2 WHERE s2.so_ID = s1.so_ID) 

Answer 3

Joe's approach should definitely work. Here are a couple of alternatives (which can be optimized and cannot be optimized):

Invert the problem to filter out those that have Open status (or! Closed, depending on how many statuses you have):

 SELECT T1.Id FROM Table as T1 LEFT JOIN (SELECT Id FROM Table WHERE Status <> 'Closed') as T2 ON T1.Id = T2.Id WHERE T2.Id IS NULL 

Use MAX and MIN as grouping functions:

 SELECT Id FROM Table GROUP BY Id, Status HAVING MAX(Status) = 'Closed' 

Use 2 views:

 SELECT C.Id FROM ( SELECT Id FROM Table WHERE Status = 'Closed' ) as C LEFT JOIN ( SELECT Id FROM Table WHERE Status = 'Open' ) as O ON C.Id = O.Id WHERE O.Id IS NULL 

I would suggest that the 2 LEFT JOIN approaches will be optimized in the best way, and then the MAX and then COUNT versions - but you definitely need a profile if performance is important to you. Lack of performance considerations, I personally think that 2 views are the most readable - others may not agree.

Answer 4

You can use the ALL operator:

 SELECT DISTINCT Sales_Order_ID FROM sales_order_table t1 WHERE 'Closed' = ALL ( SELECT Sales_Order_Line_Staus FROM sales_order_table t2 WHERE t1.sales_order_id = t2.sales_order_id ) 

In plain English: select those Sales_Order_ID for which all related rows have the same status, and this status has the value "Closed".

---

If you want any status, you can easily do this ...

 SELECT DISTINCT Sales_Order_ID, Sales_Order_Line_Staus FROM sales_order_table t1 WHERE Sales_Order_Line_Staus = ALL ( SELECT Sales_Order_Line_Staus FROM sales_order_table t2 WHERE t1.sales_order_id = t2.sales_order_id ) 

... or even this (if you are not interested in the actual Sales_Order_Line_Staus ):

 SELECT Sales_Order_ID FROM sales_order_table GROUP BY Sales_Order_ID HAVING COUNT(DISTINCT Sales_Order_Line_Staus) = 1