Pratik Joy Pratik Joy - 1 month ago 9
SQL Question

Finding drivers from nearest locations of a given location

I have 3 tables.

Table 1 - drivers

Table 1 - drivers

Table 2 - location

Table 2 - location

Table 3 - distance

enter image description here

User will search for a driver that matches a location. In drivers table the location refers to the current location of the driver. If a driver is not available in a particular location, I want a query to search for a driver that is closest to the location that the user has provide.

Table 2 is the location names and Table 3 is the distance from one place to another. But the problem is, if distance from locationid 1 to locationid 2 is stored, opposite version is not (locationid 2 to locationid 1).

Answer

This is not straight forward.

First you need to get all the location with which the given location has a relationship. This result includes the locationid and distance.

The distance with the location itself is zero. I've taken help of UNION ALL in order to make a list of <locationid,distance>.

Then make an INNER JOIN between the above list and your drivers table on matching location.

And finally sort the result set based on distance in ascending order.

SELECT 
*
FROM drivers DR 
INNER JOIN 
(
    SELECT 
    locationid,
    0 AS distance
    FROM location 
    WHERE locationname = 'Gulshan'

    UNION ALL 

    SELECT 
    IF(L.locationid = D.fromid, D.toid, D.fromid),
    D.distance
    FROM location L
    INNER JOIN distance D ON L.locationid IN (D.fromid,D.toid)
    WHERE locationname = 'Gulshan'
) AS t
ON DR.location = t.locationid 
ORDER BY t.distance 

See Rextester Demo

OR

See SQL Fiddle Demo


Note: You may include LIMIT n in order to restrict the result set containing at most top n search results.

You can also include a ..WHERE distance < MAX_ALLOWABLE_DISTANCE... in your query so that the final result makes some sort of sense.

Comments