This is all simplified, but should be enough to complete the task :-
I have a primary table;
I use a stored procedure to retrieve any providers within catchment (simplified);
SELECT id, name, location, degToMeter(st_distance(@checkPoint, location)) AS distance FROM providers WHERE degToMeter(st_distance(@checkPoint, location)) < radiusKm
However, I have now needed to add an additional table to account for additional depots;
The procedure must now return all providers that have a depot within the vicinity of a given point. The resulting fields I will need are :-
id - The provider ID
name - The provider name
depotId - ID of the provider's closest depot
location - location of the provider's closest depot
distance - The distance of the checkPoint to the selected depot
So we need a grouping of the depots for each provider, ordered by distance, with only the closest one being returned. i.e. If two depots of a single provider are within catchment, the returned provider would only show the closest one, and not return the same provider twice.
I expect this project to take a suitable candidate and hour at most to complete. I'm looking for something efficient and elegant. Many thanks.