Re: Finding orphans using SQL

[Dave Mahadevan <mahadevan@xxxxxxxx>]

Something like this. 

If customer # is a 5 digit field and order # is a 4 digit field: 

SELECT CUST#,ORDER# FROM ORDHDR WHERE CUST#*10**5+ORDER# NOT IN (SELECT
CUST#*10**5+ORDER# FROM ORDDTL)

Don Schenck wrote:

> Is there a method in SQL that can be used to find "childless" header
> records? And this would include situations where the table has a
> multi-column index as it's primary index.
> 
> Example:
> 
> OrderHeader
> -----------
> Key Fields: CustomerNbr, OrderNbr
> 
> OrderDetail
> -----------
> Key Fields: CustomerNbr, OrderNbr, LineNbr
> 
> I want to find ALL the OrderHeader records that do NOT have any OrderDetail
> records. I'm drawing a blank right now.
> 
> Peace,

Too quiet!

-- 
Thank You.

Regards

Dave Mahadevan.. mailto:mahadevan@fuse.net
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the Midrange System Mailing List!  To submit a new message,   *
* send your mail to "MIDRANGE-L@midrange.com".  To unsubscribe from     *
* this list send email to MAJORDOMO@midrange.com and specify            *
* 'unsubscribe MIDRANGE-L' in the body of your message.  Questions      *
* should be directed to the list owner / operator: david@midrange.com   *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *