Solved

SQL Master - Detail

Posted on 1998-06-05
8
140 Views
Last Modified: 2012-05-04
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
Comment
Question by:bigprop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 8

Expert Comment

by:ZifNab
ID: 1350764
Can you give a simple example?
0
 

Author Comment

by:bigprop
ID: 1350765
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
 
LVL 8

Expert Comment

by:ZifNab
ID: 1350766
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
Independent Software Vendors: 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!

 
LVL 8

Expert Comment

by:ZifNab
ID: 1350767
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
 
LVL 8

Accepted Solution

by:
ZifNab earned 50 total points
ID: 1350768
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
 
LVL 1

Expert Comment

by:Greedy
ID: 1350769
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
 
LVL 1

Expert Comment

by:Greedy
ID: 1350770
humm...that's what I get for taking so long to type it in...Damn phone
0
 

Author Comment

by:bigprop
ID: 1350771
Thanks, I knew it was possible.
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month8 days, 23 hours left to enroll

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question