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