Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
714 views
in Technique[技术] by (71.8m points)

mysql - add 3rd table to left join

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

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.

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

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

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

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.

Something like:

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

Edit:

Here's the 3 table structures:

-users2

id int(11)

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

-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)

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I'm not confident with this as I found the column names very confusing, and there is no sample data to inspect

SELECT users2.*
FROM users2
INNER JOIN user_location2 uloc ON users2.id <> uloc.uid
LEFT JOIN users_like ulike ON uloc.uid = ulike.uid1 and users2.id <> ulike.uid1 and users2.id <> uloc.uid2
WHERE users2.id = 88
AND (3959 * acos(cos(radians(28.547800068217)) 
            * cos(radians(uloc.lat)) 
            * cos(radians(uloc.lon) 
            - radians(- 82.726205977101)) 
            + sin(radians(28.547800068217)) 
            * sin(radians(uloc.lat)))) <= 25 
ORDER BY uloc.time_stamp 
## always reference with table aliases

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...