CODEWITHSUNDEEP
mysqlsql
Ace_online8888
Ace_online8888

Reputation: 25

mysql - Friend of a friend

This is a sample of my FRIENDS table

ID      FRIENDID
1001    1110    
1001    1005    
1002    1207    
1002    1188    
1005    1107    
1005    1001

I am trying to answer these questions.

Now the first one is easy

SELECT count(ID) FROM FRIENDS
WHERE friendID = 1005;

and the second one I came up with this

SELECT count(ID) FROM FRIENDS
WHERE friendID IN
(SELECT ID from FRIENDS where friendID = 1005) AND NOT ID = 1005;

The third and fourth one I get confused and came up with this but its not working. Have no idea what is right? I think I need to use DISTINCT in there to make sure there are no repeats.

SELECT count(ID) FROM FRIENDS
WHERE friendID IN
((SELECT ID from FRIENDS where friendID = 1005) AND NOT ID = 1005
WHERE friendID IN
(SELECT ID from FRIENDS where friendID = 1005) AND NOT ID = 1005);

Upvotes: 0

Views: 414

Answers (2)

Kondybas
Kondybas

Reputation: 696

  • Number of friends of a particular person.

That can done by count() function:

SELECT f1.id, count(DISTINCT f1.friendid) AS friends_cnt
  FROM FRIENDS AS f1
 WHERE f1.id = 1005;
  • Number of friends of friends of a particular person.

That can be done with joining table with itself:

SELECT f1.id, count(DISTINCT f2.friendid) AS f_friends_cnt
  FROM FRIENDS AS f1
  JOIN FRIENDS AS f2  ON f2.id = f1.frienfid
                     AND f2.id != f1.id -- to avoid recursion
 WHERE f1.id = 1005;
  • Number of friends of friends of friends of a particular person

Join table with itself twice:

SELECT f1.id, count(DISTINCT f3.friendid) AS f_f_friends_cnt
  FROM FRIENDS AS f1
  JOIN FRIENDS AS f2  ON f2.id = f1.frienfid
                     AND f2.id != f1.id 
  JOIN FRIENDS AS f3  ON f3.id = f2.frienfid
                     AND f3.id != f1.id 
                     AND f3.id != f2.id 
 WHERE f1.id = 1005;
  • Number of friends of friends of friends of friends of a particular person.

Join table with itself three times:

SELECT f1.id, count(DISTINCT f4.friendid) AS f_f_f_friends_cnt
  FROM FRIENDS AS f1
  JOIN FRIENDS AS f2  ON f2.id = f1.frienfid
                     AND f2.id != f1.id 
  JOIN FRIENDS AS f3  ON f3.id = f2.frienfid
                     AND f3.id != f1.id 
                     AND f3.id != f2.id 
  JOIN FRIENDS AS f4  ON f4.id = f3.frienfid
                     AND f4.id != f1.id 
                     AND f4.id != f2.id 
                     AND f4.id != f3.id 
 WHERE f1.id = 1005;

Upvotes: 1

Barmar
Barmar

Reputation: 782168

You may be counting the same friend multiple times, because the same person may be a friend of different friends of the original person.

Change SELECT COUNT(ID) to SELECT COUNT(DISTINCT ID) and it won't count the same ID twice.

Upvotes: 0

Related Questions