🚀 go-pugleaf

Hi,

I've been corresponding on this topic over in the programming forum
(news://forums.inprise.com/36CCC521.2AB93588%40nospam.mcbsys.com), then
realized there is a special place just for SQL!  Maybe someone here can
steer me in the right direction...

I have an application that is supposed to maintain a 1:1 relation
between two tables, Contact1 and Contact2, based on the AccountNo
field.  Sometimes this gets out of whack, and I'm trying to write a
little VDB7 routine to display (and ultimately delete) orphaned
records.  The example below looks for Contact1 records with no matching
Contact2.  

The basic question is how to identify orphaned records with SQL.  My
RIGHT OUTER JOIN checking a field in the "left-side" table for NULL
doesn't seem to bring back any rows.

My sample contact1 has 1 record, and contact2 has 0 records, i.e.
Contact1's record is an orphan.  When I run this code:

SELECT *
FROM "M:\GM4Trados\Test\contact2.dbf" Contact2
   RIGHT OUTER JOIN "M:\gm4trados\Test\contact1.dbf" Contact1
   ON  (Contact2.ACCOUNTNO = Contact1.ACCOUNTNO)  
WHERE  (Contact2.ACCOUNTNO IS NULL)  

I get no rows in the result.  However, this old-fashioned approach:

use contact1
use contact2 in select() order contact2
set relation to accountno into contact2
browse

does in fact give me a row to browse.

After reading the posts here under the "Query troubles" topic, I'm
wondering whether this whole thing may have to be re-written
anyway--will I be able to delete the orphaned records if I ever get this
JOIN to behave?

Thanks,

Mark Berry