Home Mysql limit on left join results
Reply: 0

Mysql limit on left join results

user1356
1#
user1356 Published in June 19, 2018, 4:28 pm

I am trying to find our nearby branches that can serve a list of our customers (company_id) locations. The two tables (customers 'locations' and our 'branches') both have indexed lat / lng fields. This MySQL query works using the haversine formula and returns all branches within 25 miles of each location.

select locations.id,locations.street, locations.city, locations.state,branches.id, branchname,branches.city,branches.state,
(3956 * acos(cos(radians(locations.lat)) 
                     * cos(radians(branches.lat)) 
                     * cos(radians(branches.lng) 
                     - radians(locations.lng)) 
                     + sin(radians(locations.lat)) 
                     * sin(radians(branches.lat)))) as branchdistance
from locations
left join branches on 
                   (3956 * acos(cos(radians(locations.lat)) 
                     * cos(radians(branches.lat)) 
                     * cos(radians(branches.lng) 
                     - radians(locations.lng)) 
                     + sin(radians(locations.lat)) 
                     * sin(radians(branches.lat)))) < 25
where locations.company_id = 388
order by locations.id, branchdistance

However I want to limit the number of branches (left join) returned to a max of 5. Any help would be greatly appreciated. Thanks

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.455687 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO