Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

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
0
bigprop
Asked:
bigprop
  • 4
  • 2
  • 2
1 Solution
 
ZifNabCommented:
Can you give a simple example?
0
 
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
0
 
ZifNabCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now