"people you may know" sql query

Question

I'm working on a "people you may know" feature. I have two tables:

USERS
id
email
name
etc

FRIENDSHIPS
user_id
friend_id

For each friendship I make two records. Say users 7 and 9 become friends... I would make a record where user_id=7,friend_id=9 and another where user_id=9, friend_id=7 in the friendships table.

How would I make a sql query that suggests people I'm likely to know based on friends of my friends? I also want it ordered based on the most mutual friends.

Answer 1

select u.id, u.email, u.name, u.etc
-- Get all my friends
from Friendships as f1
-- Get their friends
inner join Friendships as f2
    on f1.friend_id = f2.user_id
-- Get their friends User information
inner join Users as u
    on f2.friend_id = u.id
where f1.user_id = @userId

Would be where I would start.