Farouk,

See this message for excellent idea from Al Doman Forum: Visual FoxPro Category: Coding, syntax & commands Title: Re: Quick way to compare two arrays Thread #503959 Message #503969

You have to do three selects:
1)

Select empNO from table1 inner join Table3 on Table1.EmpNo=Table2.EmpNo ; && this way you get intersection's IDs 
into cursor curIntersection

2) select * from Table1 where empNO in (select EmpNO from curIntersection) ;
union ;
select * from Table2 where empNO in (select EmpNO from curIntersection) ;
into cursor curCombined

3) select *, count(*) as cnt_dif from curCombined ;
group by 1,2,3,... (number of fileds) ;
having count(*)=1 ; && Differences
into cursor curDifferences



>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