CODEWITHSUNDEEP
mysqlself-join
D.k
D.k

Reputation: 462

How to find second level connections of a user in mysql like linkedin?

I need to write a query to fetch second level connections of a user.

Suppose there are four friends:A, B, C, D

A is friend with B
B is friend with C and D

Now i want to find second level connections for A i.e C and D

Database Design

Table: users

id name
1  A
2  B
3  C
4  D

Table: user_friends

id  friend1  friend2
1    1        2
2    2        3
3    2        4

Anyone to help me with query?

Upvotes: 1

Views: 131

Answers (1)

OscarJ
OscarJ

Reputation: 413

This would work:

SELECT f.name
FROM users AS u, user_friends AS a, user_friends AS b, users as f
WHERE u.name='A' AND u.id != f.id AND
  ((u.id = a.friend1 AND a.friend2 = b.friend1 AND b.friend2 = f.id) OR
   (u.id = a.friend1 AND a.friend2 = b.friend2 AND b.friend1 = f.id) OR
   (u.id = a.friend2 AND a.friend1 = b.friend1 AND b.friend2 = f.id) OR
   (u.id = a.friend2 AND a.friend1 = b.friend2 AND b.friend1 = f.id));

Upvotes: 1

Related Questions