Re: Finding Orphans

One thing you could try is to group the child tables before you do the nested select, i.e.

SELECT count(*)

    FROM address
    WHERE addr_key NOT IN

            (
            SELECT addr_key FROM client
                UNION
            SELECT addr_key FROM employee
                UNION
            SELECT addr_key FROM groups
                UNION
            SELECT addr_key FROM patient
                UNION
            SELECT addr_key FROM provider
            )

I'm not sure this will help because I'm not sure why your original version isn't working right. This query should be more efficient, unless the query optimizer has already figured things out: you only want to scan all the child tables once, then form a single unique set of IDs to compare against. That's what this should do.

The first question I always ask myself when I start bloodying my forehead is: is this really wrong? Perhaps you don't actually have any Orphans.

"Lucky Leavell [RIS]" <ris_at_iglou.com> wrote in message news:Pine.GSO.4.31.0101081607350.21269-100000_at_shell1...
> I have a situation whereby I am trying to determine the number of orphans
> in an addresses table by checking for those address keys NOT IN the five
> tables which use it, to wit:
>
>
> SELECT count(*)
> FROM address
> WHERE addr_key NOT IN (SELECT addr_key
> FROM client)
> AND addr_key NOT IN (SELECT addr_key
> FROM employee)
> AND addr_key NOT IN (SELECT addr_key
> FROM groups)
> AND addr_key NOT IN (SELECT addr_key
> FROM patient)
> AND addr_key NOT IN (SELECT addr_key
> FROM provider)
>
> These tables contain anywhere from a few hundred to a few million rows and
> only the address and patient tables have an index on the addr_key column.
> After running three hours the query returns a zero count which I know is
> NOT correct.
>
> After pulling hair (and I don't have that much left to pull!), I still
> cannot see what I am missing. Any ideas? Is there a better way to do
> this?
>
> Thank you,
> Lucky
>
> Lucky Leavell Phone: (800) 481-2393 or (812) 366-4066
> UniXpress - Your Source for SCO FAX: (888) 231-9640 or (812) 366-3618
> 1560 Zoar Church Road NE Email: lucky_at_UniXpress.com
> Corydon, IN 47112-7374 WWW Home Page: http://www.UniXpress.com
>
Received on Tue Jan 09 2001 - 07:45:04 CET