SQL Master - Detail

Posted on 1998-06-05
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
Question by:bigprop
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

Expert Comment

ID: 1350764
Can you give a simple example?

Author Comment

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

Expert Comment

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.
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!


Expert Comment

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'))

Accepted Solution

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.

Expert Comment

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'

Expert Comment

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

Author Comment

ID: 1350771
Thanks, I knew it was possible.

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Correct Component for Shopping Cart. 2 121
control image tags in a string ? 12 153
Not able to call Delphi XE10 dll function from Delphi 6.0 4 48
RESTRequest Parameter 4 53
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…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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