"people you may know" sql query

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 al开发者_如何学Cso want it ordered based on the most mutual friends.

---

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.

---

Select friend_id from friendships where user_id IN (select friendid from freindships where userid = <userid>)

Considering user 7 and 9 are friends. For user 7, this query would give you the list of friends of user 9.

---

select * from 
(select distinct friendsOfFriends.friends_id, count(1) as mutualfriends
  from FRIENDSHIPS friendsOfFriends
 inner join FRIENDSHIPS friends ON friends.friend_id = friendsOfFriends.user_id
  where friends.user_id = @myuserid
  group by friendsOfFriends.friends_id) t
order by mutualfriends desc

---

You can use the query below;

Query:

Select * from [USERS] U where id not in 
((select F.friend_id as id from [FRIENDSHIPS] f where f.user_id='" + Session["UserId"] + "' and f.Status=1)
union (select F.user_id as id from [FRIENDSHIPS] f where f.friend_id='" + Session["UserId"] + "' and f.Status=1)
union (select F.friend_id as id from [FRIENDSHIPS] f where f.user_id='" + Session["UserId"] + "' and f.Status=0)
union (select F.friend_id as id from [FRIENDSHIPS] f where f.user_id='" + Session["UserId"] + "' and f.Status=2))
and U.id !='" + Session["UserId"] + "'

NOTE: Status may be "1", "0", "2" as a default parameters. The meaning of them are;

1: Confirmed 0: Not Confirmed yet 2: Denied

I wrote query code above considering by these parameters. Yours may be like this, too.

Take it easy!