SQL selecting people you may know

`The question you're asking appears subjective and is likely to be closed.` I wasn't surprised when I saw above horrible warning while I was filling in title field. I read almost every thread talking about `friends of friends` or `mutual friends` but I'm not sure I found the right solution that I want to do. I'm sorry I'm not good at English nor SQL. How can I find the right answer while being not good at both of languages? I decided I have to ask. I won't let myself down for `down-vote`s or any `duplication warning`s. As I want the answer, I'll write down as sincerely as possible for any further similar problems can be helped. I have a table for friend relations. ``` FRIEND (TABLE) ----------------------------------- PLAYER_ID(PK,FK) FRIEND_ID(PK,FK) ----------------------------------- 1 2 // 1 knows 2 2 1 // 2 knows 1 1 3 // 1 knows 3 2 3 // 2 knows 3 2 4 // 2 knows 4 2 5 // 2 knows 5 // updated 3 5 // 3 knows 5 // updated 1 100 1 200 1 300 100 400 200 400 300 400 ``` Both `composite primary keys` are also foreign keys from `PLAYER` table. I asked and got answered from such nice people for "people know each other". SQL view for acquaintance from table. And I have a view like this. ``` ACQUAINTANCE (VIEW) ----------------------------------- PLAYER_ID(PK,FK) FRIEND_ID(PK,FK) ----------------------------------- 1 2 // 1 knows 2 2 1 // 2 knows 1 ``` As you might be noticed, this relationships' business logic has following two purposes. - One player can say he or she knows someone else. - When both people say they know each other, they can be said as acquaintance. And, now, I want to know is there any good way for - Selecting other PLAYER_IDs - With given PLAYER(PLAYER_ID) (say 1) - Which each is one of `friends of given PLAYER's direct friends' - Which each is not the PLAYER himself (excluding 1 -> 2 -> 1) - Which each is not the PLAYER's direct friends (excluding 3 from 1 -> 2 -> 3 by 1 -> 3) - Order by number of mutual friends if possible. I think the Justin Niessner's answer in "people you may know" sql query is the closest path that I must follow. Thanks in advance. I'll close the thread if this subject is really duplicated and not necessary. UPDATE -------------------------------------------------------------- for Raphaël Althaus's comment `whose name is same with my future daughter` (is it boy's name?), 3 is a candidate for `friends of friends of 1` because ``` 1 knows 2 2 knows 3 ``` but excluded because ``` 1 already knows 3 ``` Basically I want to serve for the `given player` the ``` people he or she may know which is not himself or herself // this is nothing but obvious which each is not already known to himself ``` With above table ``` by 1 -> 2 -> 4 and 1 -> 3 -> 5 4 and 5 can be suggested for 1 as 'people you may know' order by number of mutual friends will be perfect but I don't think I can understand even if someone show me how. sorry. ``` Thank you. UPDATE --------------------------------------------------------------------- I think I must try step by step by myself from what I've learned `FROM HERE WITH VARIOUS PEOPLE` even if it's not the right answer. Please let me know if I'm doing anything wrong. First of all, let me self join the FRIEND table itself. ``` SELECT * FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID ``` prints ``` +-----------+-----------+-----------+-----------+ | PLAYER_ID | FRIEND_ID | PLAYER_ID | FRIEND_ID | +-----------+-----------+-----------+-----------+ | 1 | 2 | 2 | 1 | | 1 | 2 | 2 | 3 | | 1 | 2 | 2 | 4 | | 1 | 2 | 2 | 5 | | 1 | 3 | 3 | 5 | | 2 | 1 | 1 | 2 | | 2 | 1 | 1 | 3 | | 2 | 3 | 3 | 5 | +-----------+-----------+-----------+-----------+ ``` F2.FRIEND_ID only ``` SELECT F2.FRIEND_ID FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID ``` prints ``` +-----------+ | FRIEND_ID | +-----------+ | 1 | | 3 | | 4 | | 5 | | 5 | | 2 | | 3 | | 5 | +-----------+ ``` for 1 only ``` SELECT F2.FRIEND_ID FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID WHERE F1.PLAYER_ID = 1; ``` prints ``` +-----------+ | FRIEND_ID | +-----------+ | 1 | | 3 | | 4 | | 5 | | 5 | +-----------+ ``` not 1 ``` SELECT F2.FRIEND_ID FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID WHERE F1.PLAYER_ID = 1 AND F2.FRIEND_ID != 1; ``` prints ``` +-----------+ | FRIEND_ID | +-----------+ | 3 | | 4 | | 5 | | 5 | +-----------+ ``` not 1's direct knowns ``` SELECT F2.FRIEND_ID FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID WHERE F1.PLAYER_ID = 1 AND F2.FRIEND_ID != 1 AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1); ``` prints ``` +-----------+ | FRIEND_ID | +-----------+ | 4 | | 5 | | 5 | +-----------+ ``` I think I'm getting there. UPDATE ----------------------------------------------------------------- Following paths added ``` 1 -> 100 -> 400 1 -> 200 -> 400 1 -> 300 -> 400 ``` And the last query prints (again) ``` +-----------+ | FRIEND_ID | +-----------+ | 4 | | 5 | | 5 | | 400 | | 400 | | 400 | +-----------+ ``` at last, I got the candidates: 4, 5, 400 Putting `distinct` surely work for the primary goal ``` SELECT DISTINCT F2.FRIEND_ID FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID WHERE F1.PLAYER_ID = 1 AND F2.FRIEND_ID != 1 AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1); ``` prints ``` +-----------+ | FRIEND_ID | +-----------+ | 4 | | 5 | | 400 | +-----------+ ``` And, now, ordering by mutual counts needed. Here comes the number of mutual friends for each candidates. ``` +-----------+ | FRIEND_ID | +-----------+ | 4 | 1 (1 -> 2 -> 4) | 5 | 2 (1 -> 2 -> 5, 1 -> 3 -> 5) | 400 | 3 (1 -> 100 -> 400, 1 -> 200 -> 400, 1 -> 300 -> 400) +-----------+ ``` How can I calculate and order by those number of mutual friends? ``` SELECT F2.FRIEND_ID, COUNT(*) FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID WHERE F1.PLAYER_ID = 1 AND F2.FRIEND_ID != 1 AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1) GROUP BY F2.FRIEND_ID; ``` prints ``` +-----------+----------+ | FRIEND_ID | COUNT(*) | +-----------+----------+ | 4 | 1 | | 5 | 2 | | 400 | 3 | +-----------+----------+ ``` I got it! ``` SELECT F2.FRIEND_ID, COUNT(*) AS MFC FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID WHERE F1.PLAYER_ID = 1 AND F2.FRIEND_ID != 1 AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1) GROUP BY F2.FRIEND_ID ORDER BY MFC DESC; ``` prints ``` +-----------+-----+ | FRIEND_ID | MFC | +-----------+-----+ | 400 | 3 | | 5 | 2 | | 4 | 1 | +-----------+-----+ ``` Can anybody please confirm this? Is that query optimal? Any possible performance problem when make it as a view? Thank you. UPDATE -------------------------------------------------------------------------------------------- I created a view as ``` CREATE VIEW FOLLOWABLE AS SELECT F1.PlAYER_ID, F2.FRIEND_ID AS FOLLOWABLE_ID, COUNT(*) AS MFC FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID WHERE F2.FRIEND_ID != F1.PLAYER_ID AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = F1.PLAYER_ID) GROUP BY F2.FRIEND_ID ORDER BY MFC DESC; ``` and tested. ``` mysql> select * from FOLLOWABLE; +-----------+---------------+-----+ | PlAYER_ID | FOLLOWABLE_ID | MFC | +-----------+---------------+-----+ | 1 | 400 | 3 | | 1 | 5 | 2 | | 2 | 100 | 1 | | 2 | 200 | 1 | | 2 | 300 | 1 | | 1 | 4 | 1 | +-----------+---------------+-----+ 6 rows in set (0.01 sec) mysql> select * from FOLLOWABLE WHERE PLAYER_ID = 1; +-----------+---------------+-----+ | PlAYER_ID | FOLLOWABLE_ID | MFC | +-----------+---------------+-----+ | 1 | 400 | 3 | | 1 | 5 | 2 | | 1 | 4 | 1 | +-----------+---------------+-----+ 3 rows in set (0.00 sec) ```