CODEWITHSUNDEEP
mysqlsql
Fenec
Fenec

Reputation: 1222

Mutual friends sql with join (Mysql)

I have two tables

users table:

id|name

user_relationships

id | user_id | friend_id

and want to get names of mutual friends of 2 users. i.e:

user_relationships
1 | 1 | 3
2 | 2 | 3  

users
3| sammy

users 1 and 2 have mutual friend 3. I want to get his name 'sammy' in one query.

How do I do that?

Upvotes: 2

Views: 5806

Answers (4)

bw_üezi
bw_üezi

Reputation: 4574

SELECT id, name
FROM users
WHERE id IN (
  SELECT friend_id
  FROM user_relationships
  WHERE user_id IN ( 1, 2 )
  GROUP BY friend_id
  HAVING COUNT(friend_id) >= 2
)

or with one join:

SELECT friend_id, name
FROM user_relationships r
  INNER JOIN users u ON r.friend_id = u.id
WHERE user_id IN ( 1, 2 )
GROUP BY friend_id
HAVING COUNT(friend_id) >= 2

Upvotes: 6

Raffael
Raffael

Reputation: 20045

didn't check it but this query should give you a list of related user names for ID [no].

select u1.name, u2.name 
  from users as u1
  join user_relationships as ur
    on u1.id = ur.user_id
  join users as u2
    on ur.friend_id = u2.id
  where U1.id = [no];

BTW, you don't need an artificial id for your cross-table, as (user_id, friend_id) is already a legitimate primary key.

Upvotes: 0

Nivas
Nivas

Reputation: 18364

You need to join user_relationships with itself, so that two rows with different user_id have the same friend_id

All mutual friends:

select ur1.user_id user1, 
       ur2.user_id user2, 
       ur2.friend_id mutual_friend
from   user_relationships ur1 
       JOIN user_relationships ur2 ON  ur1.friend_id = ur2.friend_id
where  ur1.user_id != ur2.user_id

Join with users table to get the names:

select ur1.user_id user_id1, 
        u1.name User1, 
       ur2.user_id user2, 
        u2.name User2,
       ur2.friend_id mutual_friend_id,
        u3.name mutual_friend
from user_relationships ur1 
     JOIN user_relationships ur2 ON  ur1.friend_id = ur2.friend_id
     JOIN user u1 ON u1.user_id = ur1.user_id
     JOIN user u2 ON u1.user_id = ur2.user_id
     JOIN user u3 ON ur1.user_id = u3.user_id
where ur1.user_id != ur2.user_id

You can filter for mutual friends for some specific users using ur1.user_id = first_user and ur2.user_id = second_user

Upvotes: 6

Emiswelt
Emiswelt

Reputation: 4009

You could try something like this: 

select id, name from users where id in 
    (select friend_id from user_relationships where user_id = @user1_id and friend_id in 
        (select friend_id from user_relationships where user_id = @user2_id)
    )

This should return all mutual friends of users with the IDs @user1_id and @user2_id. It's not tested yet, but should provide a starting point...

Upvotes: 0

Related Questions