Re: Finding Orphans

"Heinz Huber" <Heinz.Huber_at_elbanet.co.at> wrote in message news:3A5B2907.F2B60833_at_elbanet.co.at...
> Paul Keister wrote:
> >
> > 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)

>

You're probably right, especially since there is not going to be much, if any overlap, i.e. very few records will share and address. Received on Tue Jan 09 2001 - 22:15:23 CST