Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple Question... Not so simple answer....

Author  Topic 

steve.greig
Starting Member

6 Posts

Posted - 2014-09-08 : 10:56:54
Wondering if anyone can help me with this...

All i'm trying to get is "show me all orders that are delivered orders that do not have a delivery charge"

I have 2 tables. OrderHeader and OrderLine.

On the OrderHeader table there is a SaleType column where i can specify that i want delivered orders only (WHERE SaleType = 3).
The OrderLine Table gives me the detail (including ProductID) of each order line for every order.

In words if i break it down into 2 parts... it would be:

1 - show me all delivered orders (simple enough)
2 - of those orders, show me the OrderID's of the ones that do not have the delivery charge productID (lets say 100) on them which will somehow need to be retrieved from the OrderLine table.

Is this possible? Is sounds simple to say.. but i cant seem to covert it in to SQL.


Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-09-08 : 11:05:53
Maybe something like the following. If not, post some sampe data in consumable format along with desired results.

SELECT *
FROM OrderHeader H
WHERE SaleType = 3
AND NOT EXISTS
(
SELECT 1
FROM OrderLine L
WHERE L.OrderID = H.OrderID
AND L.ProductID = 100
)
Go to Top of Page
   

- Advertisement -