I’m using mysql. There is likes_table for users. However, I am stuck to find people who like each other.
likes_table:
x
id (unique), from_user_id, to_user_id
1,3,1
2,1,3
3,1,64
4,1,2
5,2,1
For example, I want to find mutual likes for from_user_id =1 Therefore expected result should be:
2,3
I tried to connect two columns with IN command.
SQL code I tried:
Select to_user_id from likes_table where from_user_id IN (Select from_user_id from likes_table where to_user_id = #id)
This gives only user_id=1
Advertisement
Answer
One method is to filter the table for two conditions:
- Row where the
from_user_id = 1
; and, - The reciprocal row exists.
The second condition can be handled using exists
:
select l.to_user_id
from likes l
where l.from_user_id = 1 and
exists (select 1
from likes l2
where l2.from_user_id = l.to_user_id and
l2.to_user_id = l.from_user_id
)