“朋友的朋友" SQL查询 [英] 'friends of friends' SQL query

问题描述

这个问题与上一个问题有关.您可以在此处

查看我的第一篇文章.

我正在尝试从用户表中提取数据，我需要朋友之友"，这些人与所选用户相距两步但未直接连接到所选用户

我尝试了以下查询:

select
 u.* 
 from user u 
     inner join friend f 
     on u.user_id = f.friend_id 
       inner join friend ff 
       on f.user_id = ff.friend_id 
 where ff.user_id = {$user_id} AND u.user_id <> {$user_id};

我不知道如何拉未直接连接到所选用户的用户.我得到了当前用户的朋友的所有朋友，但我也得到了当前用户的直接朋友.

解决方案

您只需要排除直接朋友和亲朋好友.我已经重新排列了表别名，这样(无论如何对我来说)就更清晰了:

SELECT
    u.*
FROM
    user u
    INNER JOIN friend ff ON u.user_id = ff.friend_id
    INNER JOIN friend f ON ff.user_id = f.friend_id
WHERE
    f.user_id = {$user_id}
    AND ff.friend_id NOT IN
    (SELECT friend_id FROM friend WHERE user_id = {$user_id})

它也消除了排除查询用户ID的需要.

This question is related to previous one. You can check my first post here

I'm trying to pull data from a user table and I need 'friends of friends', those who are two steps away from the chosen user but not directly connected to the chosen user

I tried with this query:

select
 u.* 
 from user u 
     inner join friend f 
     on u.user_id = f.friend_id 
       inner join friend ff 
       on f.user_id = ff.friend_id 
 where ff.user_id = {$user_id} AND u.user_id <> {$user_id};

I didn't know how to pull users who are not directly connected to the chosen user. I get all friends of friends of the current user, but I also get direct friends of the current user.

解决方案

You just need to exclude the ones who are direct friends as well as being friends-of-friends. I've rearranged the table aliases so it's a bit clearer (to me, anyway) what's being retrieved:

SELECT
    u.*
FROM
    user u
    INNER JOIN friend ff ON u.user_id = ff.friend_id
    INNER JOIN friend f ON ff.user_id = f.friend_id
WHERE
    f.user_id = {$user_id}
    AND ff.friend_id NOT IN
    (SELECT friend_id FROM friend WHERE user_id = {$user_id})

It also removes the need to exclude the user ID being queried.

这篇关于“朋友的朋友" SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！