Intersection between same table, auto relationship, mysql with INNER JOIN

Question

Hello, I’m making a facebook clone database. I have two tables, one of users and another friendship. The friendships table is a self relationship of users as shown in the image.

This table of friendships contains each user’s Ids and I need to select common friendships between two selected users. For example:

| idUsuario1 | idUsuario2 | | ------------- | ------------- | | 1. | 2. | | 2. | 3. | | 2. | 4. | | 1. | 3. |

I need to search in this table the common friends of user1 and user2. That is, the return will be 3.4. But I can’t properly filter the output.

Answer 1

See the querie below, it connects user 1’s friendships with user 2’s and only brings users back from intersections:

select u.* 
from amizade a1, amizade a2 , usuario u
where  a1.idUsuario2 =  a2.idUsuario2 
    and u.id  =  a1.idUsuario2
    and a1.idUsuario1  = 1 --id do primeiro usuario
    and a2.idUsuario1  = 2 --id do segundo usuario