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.
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.