Re: Finding orphans using SQL

["Greg Thielen" <gregt@xxxxxxxx>]

Don,

Here is one way to do it:

SELECT * FROM OrderHeader OH
WHERE NOT EXISTS
(SELECT * FROM OrderDetail
WHERE CustomerNbr = OH.CustomerNbr AND
OrderNbr = OH.OrderNbr)

There is another way using an exception join, but I can't remember
the exact syntax right now.  Exception and left outer joins were
fairly recent additions to SQL on the AS/400 (if you're on V3R1,
anyway).


On Thu, 1 May 1997 11:38:51 -0400, Don Schenck wrote:

>All --
>
>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,
>
>-- Don Schenck
>


-------------------------
Greg Thielen
Innovative Systems Design
gregt@isda.com
http://www.isda.com


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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   *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *