Mireille28 Mireille28 - 10 days ago 6
SQL Question

Finding mutual friend sql

actually i have 2 tables the friend table and the users table
what i try to achieve is to retreive my mutual friend by checking the friend of another user and get the data of these mutual friend from the users table

table friend is build like this

id | user1 | user2 | friend_status


then the table data looks like this

1 | 1 | 2 | 1
2 | 1 | 3 | 1
3 | 2 | 3 | 1
4 | 1 | 4 | 1
5 | 2 | 4 | 1


Then let's say that I am the user with id 2, then in that table I have 3 friends - 1, 3 and 4. What I want to retrieve are the common friends with user 1 that have also 3 friends - 2, 3 and 4 and retrieve the data from the table users for the 2 common mutual friend 3 and 4

xpy xpy
Answer

You can use a UNION to get a users friends:

SELECT User2 UserId FROM friends WHERE User1 = 1
  UNION 
SELECT User1 UserId FROM friends WHERE User2 = 1

Then, joining two of these UNION for two different Users on UserId you can get the common friends:

SELECT UserAFriends.UserId FROM
(
  SELECT User2 UserId FROM friends WHERE User1 = 1
    UNION 
  SELECT User1 UserId FROM friends WHERE User2 = 1
) AS UserAFriends
JOIN  
(
  SELECT User2 UserId FROM friends WHERE User1 = 2
    UNION 
  SELECT User1 UserId FROM friends WHERE User2 = 2
) AS UserBFriends 
ON  UserAFriends.UserId = UserBFriends.UserId