RE: Checking for Orphans

Could "addr_key" contain a NULL value? Is so, you need to add a "WHERE addr_key IS NOT NULL" to each of your SELECT statements. I have been burned in the past by this requirement. -----Original Message----- From: Lucky Leavell [RIS] [mailto:ris@iglou.com] Sent: Monday, January 08, 2001 12:32 To: informix-list@iiug.org Subject: Checking for Orphans 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? 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

On Mon, 8 Jan 2001, Bernstein, Rick wrote: > > Could "addr_key" contain a NULL value? > Is so, you need to add a "WHERE addr_key IS NOT NULL" > to each of your SELECT statements. > I have been burned in the past by this requirement. > Hmmm, good point! addr_key in address is a serial, non null key but is nullable in all the other (foreign key) tables. 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 > > 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? >