Can I do this in only one query?

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.

SELECT u1.id, COUNT(*) FROM
user as u1, user as u2, friend as f1, friend as f2
WHERE u1.id = f1.to AND u2.id = f1.from AND -- f1 and f2 are friends 
u2.id = f2.to AND f2.from = 1234 -- and f2 is my friend, so I have a mutual friend with f1
GROUP BY u1.id

EDIT1: This is just a general idea. Note that the count will be inaccurate, there will be duplicates. You'd have to play with some real data to tweak the query to eliminate all the duplicates (or find a real DBA)

EDIT2: Also I suspect that this is probably not realistic as you are essentially doing a join of with at least n^4 results for the number of users. Which will get pretty bad even if you have 100 of them. So I'm pretty sure Facebook is doing something interesting. No idea what.

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.

In this very case you don't, actually. :) That's called "redundancy". Note that if you keep only "non-duplicate" entries in this case, you can emulate what you have now with a simple query or a view. Hence, you don't need to duplicate the data in the table itself.

For your question, the answer would be something like:

SELECT User.id, COUNT(friend.to)
FROM User
LEFT JOIN friend ON User.id = friend.from
WHERE friend.to = 12345              --your target user id goes here
GROUP BY User.id
ORDER BY COUNT(friend.to) DESC