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

  • Goalie35

    SSC Eights!

    Points: 856

    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

     

  • John Mitchell-245523

    SSC Guru

    Points: 148547

    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

    Right there with Babe

    Points: 782

    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: 1627

    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: 243943

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • ScottPletcher

    SSC Guru

    Points: 98294

     

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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