CODEWITHSUNDEEP
mysqldatabasejoinlatitude-longitude
Mithran
Mithran

Reputation: 311

How to match multiple rows of table A with multiple rows of table B based on lat/lng(by distance 10) from those tables

    select tableA.*,tableB.*,
111.045* DEGREES(ACOS(COS(RADIANS(tableA.latitude)) * COS(RADIANS(tableB.latitude)) * COS(RADIANS(tableA.longitude) - 
RADIANS(tableB.longitude)) + SIN(RADIANS(tableA.latitude)) * SIN(RADIANS(tableB.latitude)))) AS distance_in_km
from table A
join(select tableB.latitude,tableB.longitude from tableB)
on tableA.id = tableB.id HAVING distance <10

This query is working but it only takes the first row from the table A and comparing that with the rows in table B. But i want each row lat/lng in table A should compare with all the lat/lng in table B.

Upvotes: 0

Views: 36

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59503

In order to join each row from tableA with each row from tableB you must do a cross join, i.e.

select tableA.*,tableB.*,
111.045* DEGREES(ACOS(COS(RADIANS(tableA.latitude)) * COS(RADIANS(tableB.latitude)) * COS(RADIANS(tableA.longitude) - 
RADIANS(tableB.longitude)) + SIN(RADIANS(tableA.latitude)) * SIN(RADIANS(tableB.latitude)))) AS distance_in_km
from tableA
cross join tableB
HAVING distance <10

Upvotes: 1

adarsh hota
adarsh hota

Reputation: 327

Please try LEFT JOIN instead of JOIN.Hope it'll work.

Upvotes: 0

Related Questions