SQL获取不是我的朋友的朋友的所有朋友 [英] Sql to get all the friends of friends who's not my friend
问题描述
我正在研究可能的朋友"功能.我需要向所有不是我的朋友的朋友显示所有朋友,也不要向我发送或没有我的待处理请求
I'm working on a "Possible Friends" feature. where i need to show all the friends of friends who's not my friend and also not send me or not having my pending request
FRIENDSHIPS
user_id
friend_id
status (0 = pending, 1 = approved)
对于每一次友谊,我都会做两个记录.假设用户1和2成为朋友...我要在friendships
表中记录一个where user_id=1,friend_id=2
和另一个where user_id=2, friend_id=1
.
For each friendship I make two records. Say users 1 and 2 become friends... I would make a record where user_id=1,friend_id=2
and another where user_id=2, friend_id=1
in the friendships
table.
当第一个用户发送请求时,将状态设置为0,当朋友接受请求时,我将两行都更新为1
when first user send request, set status to 0 and when friend accept request then I would update both row with 1
我将如何基于我的朋友的朋友进行一个建议可能的朋友"的sql查询?
How would I make a sql query that suggests "possible friends" based on friends of my friends ?
推荐答案
在这里,您可以...简单加入
Here you go... simple join
SELECT F2.friend_id
FROM FRIENDSHIPS F
JOIN FRIENDSHIPS F2 ON F.friend_id = F2.user_id
WHERE F2.friend_id NOT IN (SELECT friend_ID FROM FRIENDSHIPS WHERE user_id = @user_id)
AND F.user_id = @user_id
这篇关于SQL获取不是我的朋友的朋友的所有朋友的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!