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
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

ZifNabConnect With a Mentor Commented:
Hi bigprop,

This should it be, sorry for my BIG mistakes in the first try!

SELECT o.CustomerCode
FROM orders o
WHERE (o.OrderCode not IN (SELECT orderCode FROM items where ItemStatus = 'ORD'))

Hope this is what you want, I tested it and it gives as a result set : 123, 569

Regards, Zif and happy programming.
Can you give a simple example?
bigpropAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
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'))
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'
humm...that's what I get for taking so long to type it in...Damn phone
bigpropAuthor Commented:
Thanks, I knew it was possible.
All Courses

From novice to tech pro — start learning today.