question

c_LG avatar image
c_LG 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
sqlquerymysqlsub-queryhelp
capture.png (31.0 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What have you tried thus far?
0 Likes 0 ·

1 Answer

· Write an Answer
Jon Crawford avatar image
Jon Crawford answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.