Re: SQL - Finding Orphans?

SELECT dt_caseid, dt_deseqnoptr FROM u_dkttext WHERE NOT EXISTS ( SELECT de_caseid FROM u_dktentry WHERE u_dktentry.de_caseid = u_dkttext.dt_caseid AND u_dktentry.de_seqno = u_dkttext.dt_deseqnoptr ) -- Regards, Doug Lawry www.douglawry.webhop.org "William Fields" <Bill_Fields@azb.uscourts.gov> wrote in message news:cqcet7$qal$1@apollo.nyed.circ2.dcn... > Hello, > > I'm having a hard time coming up with an SQL statement that returns just > orphan records. Here's what I've got (don't worry too much about the format, > I'm issuing this statement from Visual FoxPro): > > SELECT dktentry.de_caseid, dktentry.de_seqno, ;> dkttext.dt_caseid, dkttext.dt_deseqnoptr ; > FROM informix.u_dkttext, ; > OUTER informix.u_dktentry ; > WHERE (U_dkttext.dt_caseid = U_dktentry.de_caseid ; > AND U_dkttext.dt_deseqnoptr = U_dktentry.de_seqno) ; > AND U_dktentry.de_caseid IS NULL > > My problem is if I include the last line: > > AND U_dktentry.de_caseid IS NULL > > The resultset brings in everything on the u_dktentry side in as .NULL. (an > incorrect result), if I leave it out, I get the entire u_dkttext recordset > including the orphans. VFP allows OUTER joins using an ANSI JOIN clause, and > the WHERE clause is evaluated after the join, so that's what I'm more > familiar with. Having to combine a join condition and filter all in the same > WHERE isn't working for me. This is more of an academic question since I > found what I was looking for by leaving out the IS NULL part and copying the > resulting .NULL. records off to another table, but it peaked my curiosity > enough to post. > > How can I structure the SQL so that it only returns the orphaned u_dkttext > records? > > Thanks. > -- > William Fields > MCSD - Microsoft Visual FoxPro > US Bankruptcy Court > Phoenix, AZ > > You get what you measure, so be careful about it. > > - Bob Lewis