将第三个表添加到左连接 [英] add 3rd table to left join

问题描述

我有一个 mysql 查询，可以获取给定点一定距离内的用户

I have a mysql query that fetches users within a certain distance of given point

SELECT users2.*
FROM users2
LEFT JOIN user_location2 
ON user_location2.uid = users2.id 
WHERE ( 3959 * acos( cos( radians(28.547800068217) ) * cos( radians( `lat` ) ) * cos( radians( `lon` ) - radians(-82.726205977101) ) 
+ sin( radians(28.547800068217) ) * sin( radians( `lat` ) ) ) )  <= 25
AND
ORDER BY time_stamp

我希望添加第三个表 (user_like) 以消除许多不应该包含在结果中的可能行.

I'm hoping to add in a 3rd table (user_like) to eliminate a lot of possible rows that shouldn't be included in the result.

假设脚本正在为 user_id = 88 运行

Let's say the script is running for user_id = 88

[![这是表的状态][1]][1]

[![Here's the state of the table][1]][1]

我在调用第三个表或根本不包含它时遇到问题.

I'm having issues calling the third table or including it at all.

所以基本上用户 89、90 和 91 会落在位置半径范围内，但不会被包含在结果中，因为用户 88 已经喜欢他们了.

So basically users 89, 90 and 91 would fall under the location radius, but wouldn't be included in the result because user 88 already liked them.

类似于:

WHERE user_like.uid1 = 88 AND user_like.uid2 != (89,90,91)

这是 3 个表结构:

-users2

id int(11)

(其他列已被省略，因为在这种情况下它们不相关 select *)

(other columns have been omitted because they are irrelevant in this case select *)

-user_location2

-user_location2

uid int(11)
lat double
lon double
time_stamp  bigint(20)

-user_like

uid1  int(11)
uid2  int(11)
time_stamp  bigint(20)

为了澄清一点，我有一个用户发送了一个请求(假设是用户 88)，该请求应该返回距离他们 25 英里范围内的所有用户(这部分目前有效).我要添加的是考虑 user_like 表的查询.如果用户 88 已经喜欢"了另一个用户(uid1, uid2)，那么应该从查询结果中删除它，因为用户 88 已经看过并喜欢过那个用户.

To clarify a bit more, I have a user sending up a request (let's say user 88) that should return all users within 25 miles of them (this part currently works). What I'm trying to add is the for the query to take into considering the user_like table. If user 88 already "liked" another user (uid1, uid2) then that should be removed from the query result because user 88 has already seen and liked that user.

推荐答案

试试这个:

SELECT users2.*
FROM users2
LEFT JOIN user_location2 
ON user_location2.uid = users2.id 
LEFT JOIN users_like ul ON ul.uid1 = users2.id
WHERE ( 3959 * acos( cos( radians(28.547800068217) ) * cos( radians( `lat` ) ) * cos( radians( `lon` ) - radians(-82.726205977101) ) 
+ sin( radians(28.547800068217) ) * sin( radians( `lat` ) ) ) )  <= 25
AND users2 != ul.uid2
ORDER BY time_stamp

这篇关于将第三个表添加到左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！