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

Navigation
0

Hello, I'm doing 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 picture.

This Friendship table contains the IDs of each user and needs to select the mutual friendships between two selected users. For example:

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

I need to look up this table for the common friends of user1 and user2. That is, the return will be 3.4. But I can not properly filter the output.

    
asked by anonymous 24.10.2018 / 22:14

1 answer

0

See the query below, it connects the friendships of user 1 with those of user 2 and only brings users to 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
    
24.10.2018 / 22:36
How to delete an object of type Date in the Angular How to create a table in Oracle using foreign key