asked
How do I write this query?
I have a table containing orders and order items. I pulled a small order to allow you to see what I am referring to. ![alt text][1] When looking at the attachment: - [Order Number] is just the order number - [Unique ID] is the unique ID assigned to each item within the order. - [Asm Main] represents if the item is a main item or a sub item. - [Asm Num] is the number that links sub items to its main item. (both the sub and main item will have the same [Asm Num]) - [Item Number] was selected to make it easier to understand how main/sub items are linked. - [Date Promised] and [Ship Date] are the two dates I need to focus on. Here's what I'm trying to do. I want to the pull the main items when - the main item has a [Date Promised] field that IS NULL - and ALL the sub items for that main item have [Ship Date] that IS NOT NULL. [1]: /storage/temp/4480-capture.png
try using NOT EXISTS (SELECT foo FROM bar WHERE [Ship Date] IS NULL and bar.[asm num]=outerTable.[asm num]) make sure to reference that outer table in the SELECT within the EXISTS() so it looks at the same thing in both tables. Post what you have after trying that, we'll try to help you get it right. Preferably with DDL and data in temp tables we can use for troubleshooting
19 People are following this question.
