Sql query to get mutual friends

Question

I have two tables one is called users ( id,first name,last name...) second one is called followings which contains data about which user is following other users

Followings table example

userId   followerId
6           10
6           2
6           3
8           1
8           2
8           3

How do I get number of mutual friends between two users

Expected result should be

first_user second_user num_of_mutual_friends
    83          73               3

Hi nikola, have you had a look at this answer? https://stackoverflow.com/questions/10043135/mysql-query-for-mutual-friends — nivlac, Mar 22 '21 at 22:29
@nivlac Yes but his table has some additional rows...that is making me confused — nikola, Mar 22 '21 at 22:36
Sample data is great, but you should also specify the expected result. — jarlh, Mar 22 '21 at 22:37
Please define "mutual friends". Does "friendship" have to do with "following"? — Gordon Linoff, Mar 22 '21 at 22:39
Friends = Followings that is the same logic followers friends etc @GordonLinoff — nikola, Mar 22 '21 at 22:42
Utterly confusing, how do you get that result from the given table data? — jarlh, Mar 22 '21 at 22:46

MTwem · Accepted Answer · 2021-03-22T22:38:50.507

0

WITH firstUser AS (
    SELECT followerId
    FROM followings
    WHERE userId = 6 
)
, secondUser AS (
    SELECT followerId
    FROM followings
    WHERE userId = 8 
)

SELECT COUNT(followerId)
FROM firstUser
INNER JOIN secondUser ON firstUser.followerId = secondUser.followerId

edited Mar 22 '21 at 22:38

answered Mar 22 '21 at 22:32

MTwem

135
1
1
9

2

This query is returning error on INNER JOIN part where equal sign is @MTwem – nikola Mar 22 '21 at 22:34
sorry, forgot the ON clause – MTwem Mar 22 '21 at 22:39

score 0 · Answer 2 · answered Mar 22 '21 at 22:46

There are multiple ways of achieving what you want, but this should do the trick:

select
  f.userId as first_user,
  s.userId as second_user,
  count(*) as num_of_mutual_friends
from
  followings f
  inner join followings s on s.followerId = f.followerId
                         and s.userId = 6
where
  f.userId = 8
group by
  f.userId,
  s.userId

You can check here a working example.

Bohemian · Answer 3 · 2021-03-22T23:17:21.543

0

Join from users to both followings:

select u.*
from users
join followings a on a.followerId = u.id
  and a.userid = 6
join followings b on b.followerId = u.id
  and b.userid = 8

edited Mar 22 '21 at 23:17

answered Mar 22 '21 at 22:53

Bohemian

412,405
93
575
722

freeguy · Answer 4 · 2021-03-23T08:53:49.813

You can also do this using subqueries. So, if you want to get the number of mutual followers between user with id 8 and user with id 6, you can use this:

SOLUTION 1:

SELECT COUNT(followerId) AS mutual_followers_count FROM followings WHERE userId = 6 AND followerId IN (SELECT followerId FROM followings WHERE userId = 8)

SOLUTION 2:

SELECT COUNT(DISTINCT(followerId)) AS mutual_followers_count FROM followings WHERE followerId in (SELECT followerId FROM followings WHERE userId = 6) AND followerId in (SELECT followerId FROM followings WHERE userId = 8)

Sql query to get mutual friends

4 Answers4