I have 3 tables.
Table 1 - drivers
Table 2 - location
Table 3 - distance
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).
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
The distance with the location itself is zero. I've taken help of
UNION ALL in order to make a list of
Then make an
INNER JOIN between the above list and your
drivers table on matching
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
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.