friend in common query mysql

Question

I have these 2 tables: users and friendship. i would like find the friends in common between two user, i tried to do some query with alias but doesn't show my results.

Table users

user_id | name   | surname
1         Luca       Jhon
2         Paul       Red
3         Jin        Blue
4         Diana      Lars

Table friendship

id_friendship | id_user_sender | id_user_receive | confirm
       1              1                2              2
       2              2                3              2
       3              1                3              2
       4              1                5              2

Should be show this one if i am the user called Luca (id 1 ) and search the realtion with Paul (id 2)

name | surname | id_user |

Jin    Blue        3

Any idea? Thank you

Answer 1

Friendship is, presumably, reciprocal. Your friendship table only has one-way relationships.

So, the idea is to create all possible friendships in both directions. Then to aggregate by the first and test the second for each of the users you are interested in:

select u.*
from (select id_user_sender as id1, id_user_receive as id2
      from frienship f
      union all
      select id_user_receive as id1, id_user_send as id2
      from frienship f
     ) f join
     users u
     on f.id1 = u.user_id
group by id1
having max(id2 = 1) > 0 and
       max(id2 = 2) > 0;