'friends of friends' SQL query

mysql, sql

Solution

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.

Problem

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.

Original source