CODEWITHSUNDEEP
mysqljoin
Matt McDonald
Matt McDonald

Reputation: 5050

Select single row based on derived value from a joined table in MySQL

I have a many-to-many relationship in MySQL with a users table and posts table linked through a third post_user table.

The posts are made at specific locations and a lat/long value is recorded alongside each post.

I want to retrieve all users and for each user their post that is closest to a given point (in the example below, co-ordinates of 43.429124/-0.294401). I only want a single post per user (they will have lots).

I've looked at similar questions/answers and tried various queries but haven't yet been able to come up with one that works quite right.

My current best effort is this..

SELECT *, (acos(cos(radians(43.429124)) * cos(radians(posts.lat)) * cos(radians(posts.long) - radians(-0.294401)) + sin(radians(43.429124)) * sin(radians(posts.lat)))) AS xdistance
FROM users
INNER JOIN post_user ON users.id = post_user.user_id
INNER JOIN posts ON posts.id = post_user.post_id
INNER JOIN (
    SELECT id, MIN(acos(cos(radians(43.429124)) * cos(radians(posts.lat)) * cos(radians(posts.long) - radians(-0.294401)) + sin(radians(43.429124)) * sin(radians(posts.lat)))) AS distance 
    FROM posts
    GROUP BY posts.id
) subposts on posts.id = post_user.post_id AND xdistance = subposts.distance

Upvotes: 0

Views: 35

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You have to find the minimum distance between all user posts, then find out which post has the same distance.

INNER JOIN (
    SELECT post_user.user_id, MIN(...) AS distance 
    FROM post_user
    JOIN posts
      ON post_user.post_id = posts.id 
    GROUP BY post_user.user_id -- minimum distance for each user
) as subposts 
   on post_user.user_id = subposts.user_id 
  AND posts.distance = subposts.distance  -- post with the same minimum distance

I don't think you have the column post.distance you probably have to calculate using the formula the same way you do on the MIN() function

Upvotes: 1

Related Questions