Pratik Joy Pratik Joy - 1 year ago 99
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 Source

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.

FROM drivers DR 
    0 AS distance
    FROM location 
    WHERE locationname = 'Gulshan'


    IF(L.locationid = D.fromid, D.toid, D.fromid),
    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


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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download