Check Mutual Friends from my friend using MySql-CodePudding

For the first table:

user_id | name
1         Me
2         Jeremy
3         Bob
4         Kelvin

For the second table:

user_id | friend_id
1         2
1         3
2         1
2         3
3         1
3         2

Therefore, the friend relationship will like this:

user_id 1 - 2,3

user_id 2 - 1,3

user_id 3 - 1,2

If let say I'm user_id 1 which is 'Me', then I need to display all my friends and show the mutual friends. Similar to facebook 'all friends' feature that will show the mutual friends.
The input will be '1' the output will be

user_id 2 return 1 mutual friends

user_id 3 return 1 mutual friends

I've been researching a long time, but have not tried out workable code.

CodePudding user response：

Use self joins and aggregation:

SELECT u.user_id, u.name, 
       COUNT(f3.friend_id) mutual_friends
FROM friends f1
INNER JOIN friends f2 ON f2.user_id = f1.friend_id
INNER JOIN users u ON u.user_id = f2.user_id
LEFT JOIN friends f3 ON f3.user_id = f1.user_id AND f3.friend_id = f2.friend_id
WHERE f1.user_id = ?
GROUP BY u.user_id, u.name;

Replace ? with user id that you want.

See the demo.