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.
| 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 HWHERE SaleType = 3 AND NOT EXISTS ( SELECT 1 FROM OrderLine L WHERE L.OrderID = H.OrderID AND L.ProductID = 100 ) |
 |
|
|
|
|
|
Simple Question... Not so simple answer....