Solved

SQL Master - Detail

Posted on 1998-06-05
8
136 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
  • 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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