Can I do this in only one query ?

Posted by Paté on Stack Overflow See other posts from Stack Overflow or by Paté
Published on 2010-12-26T15:18:52Z Indexed on 2010/12/26 15:54 UTC
Read the original article Hit count: 157

Filed under:
|

Merry christmas everyone,

I Know my way around SQL but I'm having a hard time figuring this one out.

First here are my tables (examples)

User

id
name

friend

 from //userid
 to //userid

If user 1 is friend with user 10 then you a row with 1,10. User 1 cannot be friend with user 10 if user 10 is not friend with user 1 so you have 1,10 10,1

It may look weird but I need those two rows per relations.

Now I'm trying to make a query to select the users that have the most mutual friend with a given user.

For example User 1 is friend with user 10,9 and 7 and user 8 is friend with 10,9 and 7 too ,I want to suggest user 1 to invite him (like facebook).

I want to get like the 10 first people with the most mutual friend.

The output would be like

User,NumOfMutualFriends

I dont know if that can be done in a single query ?

Thanks in advance for any help.

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql