CODEWITHSUNDEEP
mysqlsql
s78
s78

Reputation: 23

select friends of friends mysql

I have two tables:

**user**
-id
-first_name

**friends**
-id
-user_id
-friend_id

so, table 'users' have

'id'  'first_name'
 1      name_1         
 2      name_2         
 3      name_2

and 'friends'

'id'  'user_id'  'friend_id'
 1      1           2
 2      2           3
 3      3           4
 4      3           5

So I need to get 'suggested' friends, friends of friends who is not directly my friends from users table.

I tried something like this 

select distinct users.id
from friends a
join friends b on b.user_id = a.friend_id
join users on users.id = b.friend_id 
where user.id = 3

Upvotes: 0

Views: 559

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

I don't see much wrong with your query, other than you don't exclude the user for which you're searching for friends from the result set. In other words, user 3 may have friends whose friends point back to user 3, so we don't want this suggestion.

SELECT DISTINCT
    u.id,
    u.first_name
FROM friends a
INNER JOIN friends b
    ON a.friend_id = b.user_id
INNER JOIN users u
    ON u.id = b.friend_id 
WHERE a.user_id = 3 AND
      b.friend_id <> a.user_id

Upvotes: 1

Related Questions