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

Question

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

See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Aug 09 '17 at 15:36

score 1 · Accepted Answer · answered Aug 09 '17 at 15:48

1

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

answered Aug 09 '17 at 15:48

Juan Carlos Oropeza

47,252
12
78
118

Thanks for this explanation, makes a lot more sense to me now. I'm still having a problem though. I've updated my question to include distance in the original select, I've referred to it as `xdistance` to distinguish it from the `distance` in the join clause. When it was just `distance` I got results but there were more than 1 per user - I think it was using the distance from the join clause. Changing it to `xdistance` as per the question I now get: "Unknown column 'xdistance' in 'on clause'" (same if I try posts.xdistance) – Matt McDonald Aug 09 '17 at 16:09
1

That is because you cant use an alias on the same query, You need to add a subquery. or rewrite the formula on the `ON` condition. Check here https://stackoverflow.com/questions/3550435/column-alias-not-recognized-in-where-statement – Juan Carlos Oropeza Aug 09 '17 at 16:12
Switching from `AND` to `HAVING` at the end seems to be working, but I'll take a look at re-writing to be more performant. Thanks – Matt McDonald Aug 09 '17 at 16:19
You can use an alias in the `HAVING`, but not sure if return the same result you want. – Juan Carlos Oropeza Aug 09 '17 at 16:20

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

1 Answers1