>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,
Try
select T.* from Table1 T where exists (select 1 from Table2 T2
where T2.StreetNum = T.StreetNum and
T2.StreetName = T.StreetName and T2.City = T.City) into cursor table3 nofilter
If the field lengths are different, try casting the shorter field to the longer, e.g.
T2.StreetName = cast(T.StreetName as C(30))
In addition, you may want to use UPPER function to perform case insensitive comparison.
If it's not broken, fix it until it is.
My Blog