>Hi all,
>
>I have 2 tables from flat files that I have to compare if they have the same addresses:
>
>table1 with 3 fields
>streetnum streetname city
>
>12345 bob ave New City
>
>table2 with 3 fields
>streetnum streetname city
>
>12345 bob ave New City
>
>My sql syntax is :
>
>select * from table1 where streetnum in ( select streetnum from table2 ) and streetname in (select streetname from table2) and
>city in (select city from table2) into cursor table3.
>
>Please correct my sql syntax because it is not working now and the city's length are not the same for table1 and table2.
>
>Thanks in advance,
Among other things, your query will give incorrect result for the same street in diffeent cities (or the same city with different streets) For example:
table1 with 3 fields
streetnum streetname city
12345 bob ave New City
table2 with 3 fields
streetnum streetname city
12345 bob ave Old City
12345 paul ave New City
will show up as a match.
If you are looking at VFP tables you can concatenate all the fields as........
SELECT * FROM table1 ;
WHERE PADR(streetnum,100)+PADR(streetname,100)+PADR(city,100) IN;
(SELECT PADR(streetnum,100)+PADR(streetname,100)+PADR(city,100) FROM table2)
(I'm not sure of the equvalent syntax in MySql or SQL Server)