Thanks Vlad

I'm not sure if I have not phrased my question correctly. But let me give an example of the problem :

Table A
EmpNo Name
001 John
002 Amy
003 James

Table B
Empno Name
001 Peter
003 James
005 Susan

Based on above eg, we have an integrity problem bcos "001" is John in table A and Peter in table B. "003" is ok assuming all non key fields also matches. What I need to do is to create the following exception list :

Empno Name
001 John
001 Peter

For the eg above, the table is just a 2 field table and I think the following SQL should work :

SELECT * from a INNER JOIN b ON a.empno=b.empno where a.name<>b.name

However it is not feasible if the number of non key fields is a lot. In that case, the SQL would have to go like this :

SELECT * from a INNER JOIN b ON a.empno=b.empno WHERE (a.name<>b.name OR a.addrs<>b.addrs OR a.nationality<>b.nationality OR .........)

Hope to hear from u, Vlad and thanks.

Best Regards
>Hi!
>
>And, of course, ass ';' to expand VFP command to next line (damn, worked a lot with SQL Server last time ;)
>
>
>
>
>>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