select - mySQL returning different row from calculated value -
select table1.id, (select min(sqrt( pow( 69.1 * ( latitude - table1.lat ) , 2 ) + pow( 69.1 * ( table1.lon - longitude ) * cos( latitude / 57.3 ) , 2 ) )) table2 limit 1) distance table1
the result seems correct, want query return table2.id based on calculated value instead. how can achieve that?
table1 contains id, lat, lon
table2 contains id, latitude, longitude
i need smallest distance's id(table2.id) each row in table1 using data in table2.
select table1.id, (select table2.id table2 order (sqrt( pow( 69.1 * ( latitude - table1.lat ) , 2 ) + pow( 69.1 * ( table1.lon - longitude ) * cos( latitude / 57.3 ) , 2 ) )) limit 0,1 ) table2_id table1
Comments
Post a Comment