Re: SQL - Finding Orphans?

William Fields wrote: > 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): You cannot do this using the Informix OUTER JOIN syntax you have to use ANSI 92 SQL which is supported by all IDS versions later than 7.24/9.10 (so 7.30 or 9.20 and later), OR do your select without the IS NULL filter to a temp table then select from the temp table 'WHERE U_dktentry.de_caseid IS NULL'. You do not state your version info which is always helpful, if you do not have a recent release you have to use the temp table method which you can encapsulate in a stored procedure if you need to do it in one apparent step. Here's the ANSI syntax: SELECT dktentry.de_caseid, dktentry.de_seqno, dkttext.dt_caseid, dkttext.dt_deseqnoptr FROM informix.u_dkttext LEFT OUTER JOIN informix.u_dktentry ON U_dkttext.dt_caseid = U_dktentry.de_caseid AND U_dkttext.dt_deseqnoptr = U_dktentry.de_seqno WHERE U_dktentry.de_caseid IS NULL; The problem is that using the Informix syntax the engine applies the IS NULL filter at join time. At that point the NULL records to join to the unmatched inner table (u_dkttext) rows have not yet been synthesized so no match. In the ANSI 92 form the ON clause conditions are applied pre-join to filter and match rows and the WHERE clause filters are applied to the post-join result. At that point the NULL records for the unmatched rows have already been created. BTW, it may be that my suggestion to fetch the original query results into a temp table and fetch the unmatched rows from the temp table will be faster than this ANSI style query. You'll have to test it and see. Art S. Kagel > 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.