Mysql query to find friends in common

Question

I am taking over someone elses project and need to work out a php mysql query to figure out mutual friends from a friends table.

the table looks like the below - my id for example would be 1, the profile I am looking at has an id of 5

the output of the mutual friends id would be 3

rel 1 | rel 2
--------------
1     |    5
5     |    1
3     |    5
5     |    3
1     |    3
3     |    1

The id's look like they have gone in twice (both ways)

cant get my head round the query needed

can't you get every thing for 1 and 5 using sql, then use php to figure it out?? — mamdouh alramadan, Jan 01 '13 at 22:27
This may help you: http://stackoverflow.com/questions/8906154/mysql-select-mutual-friends — Nathan, Jan 01 '13 at 22:29

GolezTrol · Answer 1 · 2013-01-01T22:38:56.750

It looks like the relationships go both ways. Maybe this is an error, or maybe this system has the option to be friends with someone without them being your friend back.

Assuming the latter, you would need only to look at one of the columns, so your query could look like this:

select
  rel2
from
  YourTable
where
  rel1 in (1, 5)
group by
  rel2
having
  count(*) > 1

The count is obviously there to select only those friends that have more than one friend in the set (1, 5). The assumption is that the combination of rel1 and rel2 is unique.

If the data is indeed incorrect, you could 'solve' that by using a union on two queries on this table, to select all combinations. But mind, if this is the case, you'd better fix the data, because as you can see, your queries will quickly become unreadable (and slower, of course) if they need to fix your data.

select
  rel2
from
  (select distinct
    rel1, rel2
    from
      (select
        rel1, rel2
      from
        YourTable
      union all
      select
        rel2, rel1
      from
        YourTable) x ) y
where
  rel1 in (1, 5)
group by
  rel2
having
  count(*) > 1

Olaf Dietsche · Accepted Answer · 2013-01-01T23:10:53.603

-1

SQL Fiddle for testing

select f.friend
from (select rel1, rel2 as friend
      from friends
      where rel1 in (1, 5)
      union
      select rel2, rel1
      from friends
      where rel2 in (1, 5)) f
group by f.friend
having count(*) > 1

edited Jan 01 '13 at 23:10

answered Jan 01 '13 at 22:38

Olaf Dietsche

72,253
8
102
198

That would be a way to filter out doubles (if they are indeed doubles if the fields are swapped), but this query won't give you the mutual friends which was asked for. – GolezTrol Jan 01 '13 at 22:40
Thanks Olaf, that was spot on - appreciate everyones help – Jeff Jan 01 '13 at 23:35

Mysql query to find friends in common

2 Answers2