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.