UPDATE: Just to clarify, I ONLY need the SQL query, I do not need any PHP or other code. I have a working solution already and just need to expand the SQL. I am a developer and will add your SQL into my existing code.
I have a website that uses Google Maps to plot custom markers from a SQL database. The markers consist of store locations for ~10,000 currency suppliers in the UK. Visitors can enter their current location and see a list of currency suppliers within a 30-mile radius along with the supplier's corresponding exchange rate for a given currency.
The problem I have is some suppliers have dozens of stores within a 30-mile radius of the search location which can look messy when the markers are plotted on the map.
What I would like is to redesign the SQL query that finds these stores so that it does the following:
1) Find all stores within a 10-mile radius of the search location.
2) If there are multiple stores found for the same supplier, only return the first 5 for that supplier based on distance from the search location (i.e. the 5 closest stores).
3) Return a maximum of 50 results.
The data is stored across three tables:
Table 1 contains supplier information (name, website etc.).
Table 2 contains marker information (including lat, long coordinates) for each store.
Table 3 contains exchange rate information.
The primary key in table 1 is a secondary key in tables 2 and 3. The final query must be fairly optimised (<1 second) as it will be placed on a medium-traffic website. I am open to changing the structure of one or more tables if necessary but would prefer not to.
25 фрилансеров(-а) в среднем готовы выполнить эту работу за £158
Dear Client, Please keep trust on us we can surely do your project. We are really interested to work with you and build a healthy longterm relationship. Warm Regards, Om Infowave
Hello, I have over 15yrs experience writing SQL Code, along with lots of experience designing and implementing database solutions. I can definitely provide you with the required SQL Query for your project.