MySQL query for mutual friends
mutual-friendship, mysql, sql
Solution
Well, the only query that might work up to now is Simon's... but that's real overkill - such a complex nasty query (2 subqueries with 2 unions!) for so simple thing that you need to place a bounty? :-) And if you have like 1000+ users the query will be slow as hell - remeber, it's quadratic, and due to unions in subqueries, hardly any index would be used!
I'd suggest to re-think the design again and allow for 2 duplicate rows for a friendship:
id Person1 Person2 status
1 1 2 friend
2 2 1 friend
3 1 3 friend
4 3 1 friend
You might think that's inefficient but following simplification will allow to rewrite the query to simple joins:
select f1.Person2 as common_friend
from friends as f1 join friends as f2
using (Person2)
where f1.Person1 = '$id1' and f2.Person1 = '$id2'
and f1.status = 'friend' and f2.status = 'friend'
which will be fast as hell! (Don't forget to add indices for Person1,2.) I've advised a similar simplification (rewriting subqueries to joins) in other very nasty data structure and it has speeded up the query from eternity to blitz-instant!
So what might have been looking as a big overhead (2 rows for one friendship) is actually a big optimization :-)
Also, it will make queries like "find all friends of X" much more easier. And no more bounties will need to be spent :-)
Problem
Possible Duplicate: MYSQL select mutual friends I have a table for friendship, the friendship is stored only in one line. So there is no duplicate entries. ``` id Person1 Person2 status 1 1 2 friend 2 1 3 friend 3 2 3 friend 4 3 4 friend ``` What MySQL query (join, inner join) will help me to find common (mutual) friends between person #1 and person #3? The input in this example is {1,3} and the output should be {2} since Person #2 is friend with bot #1 and #3.