April 6, 2012

MySQL query for mutual friends

Question by george

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.

Answer by Starx

I think this is rather simply achieved by this

SELECT * FROM friends

WHERE
     (Person1 = '1' or Person2 = '1') && 
     (Person1 = '2' or Person2 = '2') &&
     status = 'friend'

Given that the you are trying to find mutual between person 1 and 2

...

Please fill the form - I will response as fast as I can!