RE: Checking for Orphans
--- "Marley, Peter" <peter.marley@acco-uk.co.uk>
> wrote:
>You could always use NOT EXISTS instead of NOT IN which is a dam site
>faster.
Which dam site did you have in mind? Roosevelt? Hoover?....
>
>Peter
>__________________________________
>Peter Marley
>Data Warehouse Developer, Acco Europe
>Tel: +44 (0)1296 732184
>Fax: +44 (0)1296 732185
>email: peter.marley@acco-uk.co.uk <mailto:peter.marley@acco-uk.co.uk>
>
>
>
> -----Original Message-----
> From: Carlos Benjamin [SMTP:benj@firstlinux.net]
> Sent: Tuesday, January 09, 2001 1:37 PM
> To: Lucky Leavell [RIS]; informix-list@iiug.org
> Subject: Re: Checking for Orphans
>
>
>
> --- "Lucky Leavell [RIS]" <ris@iglou.com>
> > wrote:
> >Version: 7.31
> >OS: AIX 4.3.3
> >
> >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?
>
> Pull someone else's hair?
>
> You could use a series of SQL queries where the first one selects
>keys into a temp table where that key doesn't appear in one of the other
>tables. Then go through the other tables and delete anything from temp that
>appears in each one of those in turn. If you're Lucky (and you seem to be)
>that should work.
> >
> >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@UniXpress.com
> >Corydon, IN 47112-7374 WWW Home Page: http://www.UniXpress.com
>
> ==
> "Outlook not so good."
> That magic 8-ball knows everything!
> I'll ask about Exchange Server next.
>
> _____________________________________________________________
> Want a new web-based email account ? ---> http://www.firstlinux.net
==
"Outlook not so good."
That magic 8-ball knows everything!
I'll ask about Exchange Server next.
_____________________________________________________________
Want a new web-based email account ? ---> http://www.firstlinux.net