Hi!

I will show this for the 1-field integer key. You can easy replace the 'key' field by expression that contains the 2 key fields as you need, as well as generate unique key.

* select all keys from a that have similar key values in b
Select a.key from a where a.key IN (select b.key from b) into cursor AllSimilarKeys

* select the records from a and b that contain unique values.
* Similar-value records will be filtered out from this result by UNION without 'All'
* {if this will not work, use 'SELECT DISTINCT FROM SimilarUnique' just after this query}
Select a.* from a where a.key in (select key from AllSimilarKeys) into cursor SimilarUnique
UNION
select b.* from a where b.key in (select key from AllSimilarKeys)

* now fix the key field value so it will be unique in the final result set for all records
&& get a max key value
select max(key) from a into array aa
select max(key) from b into array bb
if aa>bb
  mm = aa
else
  mm = bb
endif

&& use the result of query again to make it editable
use (dbf('SimilarUnique')) AGAIN alais SU in SimilarUnique

&& scan the result and replace all similar key values by unique value for both tables
select SU
mm=mm+1
scan all
  replace key with mm
  mm=mm+1 
endscan

* select all other records - the final result set
SELECT a.* from a where a.key NOT IN (select key from AllSimilarKeys) into cursor Result
UNION ALL
SELECT b.* from b where b.key NOT IN (select key from AllSimilarKeys)
UNION ALL
SELECT SU.* from SU

use in SU
use in AllSimilarKeys

Index both tables by the key field for best performance.

HTH.


>Hi VFP users/experts
>
>I need to merge 2 tables of identical structure. Before merging, I need to find rows from table 1 that match rows of table 2 in the primary key fields but not in the non key fields. The table has primary key based on 2 fields and 100 non key fields. Appreciate if you could help me construct SQL to detect rows from the 2 tables with similar primary fields but dissimilar non key fields.
>
>Thanks