CODEWITHSUNDEEP
sql
user1011625
user1011625

Reputation: 121

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

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.

Upvotes: 6

Views: 3782

Answers (4)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52157

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_IDs for which all of the associated rows have the same status, and that status happens to be '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 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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453908

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'

Upvotes: 5

Mark Brackett
Mark Brackett

Reputation: 85685

Joe's approach should definitely work. Here's a couple of alternatives (that may or may not be more optimizable) as well:

Invert the problem, to filtering out those with a status of Open (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 derived tables:

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'd suspect the 2 LEFT JOIN approaches would optimize the best, followed by the MAX versions and then the COUNT - but you should definitely profile if performance is important to you. Absent performance considerations, I personally find the 2 derived tables the most readable - others may disagree.

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135928

The basic approach here is to group by ID and status. If the count of that grouping is equal to the count by just ID, then you'll know all the rows 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 just 'closed' status, simply 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)

Upvotes: 2

Related Questions