Mutual Friends Mysql Query

Question

I'm stuck at a point in making mutual friends code for my website. I have read some of the posts here but it didn't help.

here is my mysql query:

select *
from friend_list
where uid='7'
   and status=1
   and friend_id !='3'
union
select *
from friend_list
where uid='3'
   and status=1
   and friend_id !='7' 

this displays all the friends of login user and the friends of the profile i visit. output is this ----

  id    uid     friend_id      status
  36    7         4              1
  39    7         5              1
  40    7         8              1
  1     3         4              1

from this table i want the freind_id 4 only as this is mutual.

Answer 1

I'm just starting to learn MySQL and I already solved this problem.

personID    friendID
6           10
6           2
6           3
8           1
8           2
8           3

/*  query for friends   */
select f.personID, p.personID, firstName, lastName
from person p
inner join friends f on f.friendID = p.personID
where f.personID = 6;

/*  query for common friends    */
select f1.personID 'personID 1', f2.personID 'personID 2', f1.friendID 'common friend'
from person p
inner join friends f1 on f1.friendID = p.personID
inner join friends f2 on f2.friendID = p.personID
where f1.personID = 6 and f2.personID = 8 and f1.friendID = f2.friendID;

Result:

personID 1    personID 2    common friend
6             8             2
6             8             3

Although it's not a direct answer to your code, you may take mine as a source.

Answer 2

SELECT *
FROM friend_list AS f
INNER JOIN friend_list AS mf ON f.friend_id = mf.friend_id
WHERE f.uid = 7
   AND f.status = 1
   AND mf.uid = 3
   AND mf.status = 1