How to join multiple tables (SQL) to find the difference between an order and shipped quantities?


  • I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped.

    The table hierarchy is as follows:

    • Order table contains OrderReference(PO), OrderID
    • OrderItems table contains Product/Lot/Qty requested (OrderID is parentID that joins these tables)
    • Shipping contains OrderReference(PO), ShippingID
    • ShippingArchive contains Product/Lot/Qty that was shipped (ShippingID is parent that joins these tables)

    I want to create a report that looks as follows:

    Order Requested / Shipped Items / Missing Pieces
    PC/Lot/Qty    -   PC/Lot/Qty    - PC/Lot/Qty

    I have used a temp table to get some of the data but I realize I'm taking the wrong approach. I should be able to do all of this with a single query, though it will seemingly be a bit complex.

    I get held up when I try to think about how to join where pieces don't exist, and where to perform the mathematics in my sorting and filtering. Please help!

    Sincere thanks :)

    Friday, July 12, 2013 2:40 PM


  • First, I'll point out that you likely have a flawed schema.  It appears (though we have to guess since you did not post DDL) that the PK of Order is OrderID.  I assume this because you at least mention that it is "parentID that joins these tables".  This is one reason why posting actual DDL - including constraints - can avoid a lot of confusion and delay. 

    Back to relationships.  Shipping contains OrderReference(PO).  So this column is effectively your foreign key between Shipping and Order, correct?  But the primary key of Order is OrderID, not OrderReference(PO).  Is there an actual FK relationship?  Sidenote - does "(PO)" have significance here or is it simply an abbreviation for purchase order - a term/synonym for the Order table?  Presumably the relationship between Order/Shipping is 1-0,M (1 row in Order is related to zero or many rows in Shipping) and 1-1 in the other direction (1 row in Shipping is relateed to 1 row in Order always - it is not optional/nullable).

    Next, I'm guessing that you want to see items within a order associated with the related number shipped.  If so, how do you related rows in OrderItems to rows in ShippingArchive?  Without that knowledge, no one can answer your question.  It may help to show some sample rows as well as the expected output based on those rows.  Please consider your various scenarios - orders that have not shipped at all, orders that are partially shipped, etc.  You might want to review "problem" situations - can an ordered item be "over-shipped" (more items shipped than ordered), can an item be shipped that was not ordered, etc. Lastly, can something be shipped without an associated order (or, what happens when a row in Shipping has an OrderReference value not found in Order). Perhaps it would be best if we simplify the problem and ignore the last case.

    Friday, July 12, 2013 3:15 PM