CODEWITHSUNDEEP
sqlms-access
bo-oz
bo-oz

Reputation: 2872

How to create a JOIN query that finds the closest store for each user

I've got two simple tables:

User:
id - int
name - string
lat - decimal
long - decimal

Store:
id - int
name - string
lat - decimal
long - decimal

I'd like to have a query that gets all the users with the closest shop. I don't care about the roundness of the earth, because each user will have a shop pretty close by. That's why I choose to use Pythagoras for finding the nearest distance:

SELECT 
   User.*, 
   Store.*, 
   Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
FROM User, Store

Unfortunately this gives me the Cartesian product, so that I get all the Users with the distance to every store. Is there a way to only get the closest store?

Thanks

Upvotes: 0

Views: 99

Answers (2)

forpas
forpas

Reputation: 164139

All you need is cross join the tables and a WHERE clause in which you select the store with the minimum distance from each user like this:

select u.*, s.*
from user as u, store as s
where ((u.Lat - s.Lat)^2+(u.Long - s.Long)^2) = (
  select min((u.Lat - ss.Lat)^2+(u.Long - ss.Long)^2)
  from  store as ss
)

You don't need to apply Sqr() to the calculations.

Upvotes: 0

jefftrotman
jefftrotman

Reputation: 1114

You can group by user and calculate Min(distance) and then link back to first query to figure out which store was that distance from user.

This is your query with some fields removed for clarity (and User.Id aliased)

SELECT 
    User.id as userid, 
    Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
FROM User, Store

This wraps the first query to calculate a minimum distance to a store for each user

select userid, min(distance) from (
    SELECT 
        User.id as userid, 
        Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
    FROM User, Store
 ) as x 
group by userid

Join that back to your original query to populate the user fields and figure out which store is that (minimum) distance from the user


select z.*
from (
    select userid, min(distance) as distance from (
        SELECT 
            User.id as userid, 
            Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
        FROM User, Store
     ) as x 
group by userid
) as y inner join
(
    SELECT 
        User.Id as UserId, ... (populate the remaining user fields), 
        Store.*, 
        Sqr((User.Lat - Store.Lat)^2+(User.Long - Store.Long)^2) AS distance
    FROM User, Store
) as z
on y.userid = z.userid and y.distance = z.distance

Upvotes: 1

Related Questions