MySQL query for mutual friends [duplicate]

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  status1         1          2  friend2         2          1  friend3         1          3  friend4         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_friendfrom 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 :-)

This query works with the assumption that there is no self-friending and no duplicates in friendship table, if these conditions are not meet little tweaks needed to make it work.

SELECT fid FROM (    --FIRST PERSON (X) FRIENDLIST    SELECT         (CASE WHEN Person1 = X THEN Person2 ELSE Person1 END) AS fid    FROM Friendships WHERE (Person1 = X OR Person2 = X) AND status = "friend"    UNION ALL --DO NOT REMOVE DUPLICATES WITH ALL JOIN    --SECOND PERSON (Y) FRIENDLIST    SELECT         (CASE WHEN Person1 = Y THEN Person2 ELSE Person1 END) AS fid    FROM Friendships WHERE (Person1 = Y OR Person2 = Y) AND status = "friend") FLISTGROUP BY fidHAVING COUNT(*) = 2

One more answer.

select     (case when f1.person1 = 1 then f1.person2 else f1.person1 end) as fidfrom friends f1where f1.person1 = 1 or f1.person2 = 1and f1.status = 'friend'intersectselect     (case when f1.person1 = 3 then f1.person2 else f1.person1 end) as fidfrom friends f1where f1.person1 = 3 or f1.person2 = 3and f1.status = 'friend'