MYSQL - Work out & Count mutual friends

Question

I’m having a little bit of a brain fart this evening trying to work out my logic. I need to count how many friends a person shares with one user. (Mutual friends)

I have a table with a User ID and also the User ID of a friend, an example of my layout is below:

First result meaning user1 is friends with user 2

[ID] - [FriendsID]
1-2
1-3
1-4
1-15
2-1
2-4
3-1
3-4
4-1
4-2
4-3
4-15
5-15
15-1
15-5
15-4

When my PHP Page loads, It will load the friends list for that user for example, User1. This will return the “FriendID” of (2,3,4.15)

I then need to work out how many mutual friends people have with user: 1 So for example,

1 is friends with 2,3,4
2 is friends with 1,4
3 is friends with 1,4,15

This would mean that “2” shares ONE mutual friend with 1
This would mean that “3” shares TWO mutual friend with 1

and so on

My output needs to be [FriendID] [Count]

Friend ID being the friend

Count being how many friends in common with userID 1

(Example data manually written out)

Answer 1

You can do this using a self-join on the friendsid column:

select t1.id, t2.id, count(*) as num_mutual_friends
from table t1 join
     table t2
     on t1.friendsid = t2.friendsid and t1.id <> t2.id
group by t1.id, t2.id;

I notice your data is symmetric, so (1, 2) is in the data as well as (2, 1). This means that this logic should work for you. You can add a where clause if you only care about one id.