How to find common friends in SQL

Question

I have a table like this:

CREATE TABLE friends ( 
id INT(6) PRIMARY KEY, 
user1 INT(5) NOT NULL, 
user2 INT(5) NOT NULL
); 


INSERT INTO `friends` (`id`, `user1`, `user2`) VALUES ('1', '1', '2'); 
INSERT INTO `friends` (`id`, `user1`, `user2`) VALUES ('2', '1', '3'); 
INSERT INTO `friends` (`id`, `user1`, `user2`) VALUES ('3', '1', '4'); 
INSERT INTO `friends` (`id`, `user1`, `user2`) VALUES ('4', '1', '5'); 
INSERT INTO `friends` (`id`, `user1`, `user2`) VALUES ('5', '2', '3'); 
INSERT INTO `friends` (`id`, `user1`, `user2`) VALUES ('6', '2', '4'); 
INSERT INTO `friends` (`id`, `user1`, `user2`) VALUES ('7', '3', '5'); 
INSERT INTO `friends` (`id`, `user1`, `user2`) VALUES ('8', '4', '5');

Showing:

+---+------+------+
|id |user1 |user2 |
+---+------+------+
|1  |1     |2     |
+---+------+------+
|2  |1     |3     |
+---+------+------+
|3  |1     |4     |
+---+------+------+
|4  |1     |5     |
+---+------+------+
|5  |2     |3     |
+---+------+------+
|6  |2     |4     |
+---+------+------+
|7  |3     |5     |
+---+------+------+
|8  |4     |5     |
+---+------+------+

It means that user1 and user2 are friends. I want to make a column 'common_friends' that counts common friends between user1 and user2.

'user1' has friends [2,3,4,5] and 'user2' has friends [1,3,4]. So the common friends of both users are 3,4. and then, first row of common_friends will be 2.

In this way, the table will look like this:

+---+------+------+--------------+
|id |user1 |user2 |common_friends|
+---+------+------+--------------+
|1  |1     |2     |2             |
+---+------+------+--------------+
|2  |1     |3     |2             |
+---+------+------+--------------+
|3  |1     |4     |2             |
+---+------+------+--------------+
|4  |1     |5     |2             |
+---+------+------+--------------+
|5  |2     |3     |1             |
+---+------+------+--------------+
|6  |2     |4     |1             |
+---+------+------+--------------+
|7  |3     |5     |1             |
+---+------+------+--------------+
|8  |4     |5     |1             |
+---+------+------+--------------+

How can I query this in MySQL?

In your result you have `4,1,5,2` where 2 is the common_friend from 1 and 5 but 5 does not have a friend 2. — Luuk, Oct 30 '21 at 08:33

score 0 · Answer 1 · answered Oct 30 '21 at 10:30

The problem seems to be that some friends combinations are only 1 directional in the data. F.e. does user 5 consider anyone a friend?

But you can solve that with a UNION in the joined sub-queries (or using a CTE if your mysql supports that)

SELECT t1.id, t1.user1, t1.user2
, COUNT(t3.user1) AS common_friends
FROM friends t1
LEFT JOIN (
  select user1, user2 from friends
  union
  select user2, user1 from friends
) t2 
  ON t2.user1 = t1.user2 
 AND t2.user2 != t1.user1
LEFT JOIN (
  select user1, user2 from friends
  union
  select user2, user1 from friends
) t3 
ON t3.user1 = t1.user1 
AND t3.user2 = t2.user2
GROUP BY t1.id, t1.user1, t1.user2
ORDER BY t1.id

sqlfiddle

How to find common friends in SQL

1 Answers1