Having trouble with simple select query where all ordere item statuses = shipped

Question

Having trouble with simple select query where all ordere item statuses = shipped

Goalie35

SSC Eights!

Points: 871
More actions
December 13, 2019 at 3:16 pm

#3707690

I'm trying to write a simple query but having trouble returning the correct results. I have a table of items that have been ordered. Within this table, there are several different statuses an order can be in (i.e. open, pending, shipped, cancelled, etc) I want to write a query to return the order id of any orders where ALL items have a status of 'shipped'.
Here's a sample:
Order Items Table:
OrderItemId | OrderId | ItemId | Status
111 | 123 | 999 | Shipped
112 | 123 | 888 | Shipped
113 | 456 | 777 | Shipped
114 | 456 | 666 | Open
In the above query, I want to return OrderId 123 because all items within the order have shipped. OrderId 456 still has an open item, so I don't want to include that one.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Group by OrderID and put in a HAVING clause where you check that the MAX value of Status is Shipped, and the MIN value of Status is Shipped.

John

oogibah SSC Eights! Points: 827 More actions · Answer 2

So you want to show only orders where all items of the order have the status of shipped?

This might not be the "correct" way which may have been mentioned above but this is probably how I'd do it because I'm a noob at sql

SELECT  ORDERid
      FROM OrderTable
WHERE Orderid NOT IN (SELECT orderid FROM OrderTable WHERE status <> 'Shipped')

SQL_Hacker SSCommitted Points: 1728 More actions · Answer 3

Is there an Order table? Typically, there is an Order and OrderDetail (or in your case OrderItems) to relate the order to the order items. If not, where does the OrderId come from?

Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Phil Parkin SSC Guru Points: 246965 More actions · Answer 4

SELECT o1.*
FROM OrderItem o1
WHERE NOT EXISTS (SELECT 1 FROM OrderItem o2 where o1.OrderId = o2.OrderId and o2.Status <> 'Shipped')

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101105 More actions · Answer 5

SELECT OrderId
FROM OrderItems
GROUP BY OrderId
HAVING SUM(CASE WHEN Status = 'Shipped' THEN 1 ELSE 0 END) < COUNT(*)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".