SQL QUERY Check if user Liked each other

user_9223435 0
Viewed 146

Hy I have a sql table called match_liked with 3 data, the id, the user_id, and the match_id

+----+---------+----------+
| id | user_id | match_id |
+----+---------+----------|
|  1 |     100 |       63 |
|  2 |      63 |       100|
|  3 |      45 |       77 |
|  4 |      11 |       44 |
|  5 |      33 |       2  |
+----+------+-------------+

U can see than 100 Liked 63 AND 63 liked 100 too so i want select all i tried this :

SELECT * from match_liked WHERE user_id = 100 AND match_id = 63 AND user_id = 63 AND match_id = 100

But it doesn't work, so what the correct query for check if both user liked each other ?

where-clause many-to-many select mysql sql
2 Answers

One method uses exists:

select ml.*
from match_liked ml
where exists (select 1
              from match_liked ml2
              where ml2.user_id = ml.match_id and ml2.match_id = ml2.user_id
             );

Assuming you have no duplicates, you can also use aggregation:

select least(user_id, match_id), greatest(user_id, match_id)
from match_liked
group by least(user_id, match_id), greatest(user_id, match_id)
having count(*) = 2;
user_11440350

you could do with self join,

select o1.* from match_liked o1 inner join match_liked o2
   on o1.user_id  = o2.match_id or o1.match_id = o2.user_id
user_110380080
Related