bigprop
asked on
SQL Master - Detail
We have a standard orders type system in Paradox.
Orders Table holds info re order ( date, status, customer etc)
OrderItems holds info re order items, keyed by OrderRef, ItemNumber and one of the fields is the OrderItem status.
I am looking for a query that will list records from the orders table, but only if there is NO associated record in the OrderItems table with a status of 'ORD'.
I know that I can do a join and then iterate through, rejecting those orders I find with an offending OrderItem but I would like to see if it can be done in SQL
Orders Table holds info re order ( date, status, customer etc)
OrderItems holds info re order items, keyed by OrderRef, ItemNumber and one of the fields is the OrderItem status.
I am looking for a query that will list records from the orders table, but only if there is NO associated record in the OrderItems table with a status of 'ORD'.
I know that I can do a join and then iterate through, rejecting those orders I find with an offending OrderItem but I would like to see if it can be done in SQL
Can you give a simple example?
ASKER
Say Orders Table has fields OrderCode, OrderStatus, CustomerCode
with data
1 | 4 | 123
2 | 5 | 258
3 | 4 | 569
OrderItems has fields OrderCode, ItemNumber, ItemStatus and data
1 | 1 | FUL
1 | 2 | FUL
1 | 3 | EXP
2 | 1 | ORD
2 | 2 | FUL
3 | 1 | ALL
3 | 2 | EXP
The query must return Orders info for orders 1 and 3 but not 2 because there is an associated OrderItem with status ORD
with data
1 | 4 | 123
2 | 5 | 258
3 | 4 | 569
OrderItems has fields OrderCode, ItemNumber, ItemStatus and data
1 | 1 | FUL
1 | 2 | FUL
1 | 3 | EXP
2 | 1 | ORD
2 | 2 | FUL
3 | 1 | ALL
3 | 2 | EXP
The query must return Orders info for orders 1 and 3 but not 2 because there is an associated OrderItem with status ORD
Just quick flash thinking : what about filtering table OrderItems for ItemStatus ORD and then using this result set as a query for searching Orders Table with OrderCode not in filtered table?
Mmm, i'm going to try this out.
Mmm, i'm going to try this out.
First try, does it work?
SELECT o.CustomerCode
FROM items i, orders o
WHERE (i.OrderCode=o.OrderCode)
AND (o.OrderCode not IN (SELECT orderCode FROM items where ItemStatus = 'ORD'))
SELECT o.CustomerCode
FROM items i, orders o
WHERE (i.OrderCode=o.OrderCode)
AND (o.OrderCode not IN (SELECT orderCode FROM items where ItemStatus = 'ORD'))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
give this a whack, you could probably speed this up even more by makeing a view of the secondary select...
select OrderCode from OrderItems where OrderCode not in (select distinct OrderCode from OrderItems where ItemStatus = 'ORD')
this should give you a list of all the OrderCodes that don't have any ItemStatus set to 'ORD'
select OrderCode from OrderItems where OrderCode not in (select distinct OrderCode from OrderItems where ItemStatus = 'ORD')
this should give you a list of all the OrderCodes that don't have any ItemStatus set to 'ORD'
humm...that's what I get for taking so long to type it in...Damn phone
ASKER
Thanks, I knew it was possible.