Hi My problem is that I have to list out all the unrequited relationships (A likes B but B didn't like A)
A B
1 2
2 1
3 1
In this example my solution would be the pair of 3 1 (becoz 1 likes 2 and 2 also like 1 backs) How should I express it in form of SQL?
You can formulate the query very similar to the way you have written the question. You are looking for rows of "A likes B" for which no "B likes A" rows exist.
Assuming the name of the table is mytable:
select *
from mytable t1
where not exists (
select *
from mytable t2
where t1.A = t2.B and t1.B = t2.A
);
With a self LEFT join that returns only the unmatched rows:
select t1.*
from tablename t1 left join tablename t2
on t2.a = t1.b and t2.b = t1.a
where t2.a is null
See the demo.
Results:
| A | B |
| --- | --- |
| 3 | 1 |
MySQL conveniently supports tuples with in. So one method is:
select t.*
from t
where (b, a) not in (select a, b from t);
One caution is that this will not work if a or b are NULL (but I'm guessing that cannot be the case in this example).
As a general rule, I recommend exists for this purpose, as in C.C.'s answer.
you can use NOT IN wit SLEECT all id that have a match
CREATE TABLE rel ( `A` INTEGER, `B` INTEGER ); INSERT INTO rel (`A`, `B`) VALUES ('1', '2'), ('2', '1'), ('3', '1');
SELECT A,B FROM rel WHERE A NOT IN (SELECT r1.A FROM rel r1 INNER JOIN rel r2 ON r1.A = r2.B and r1.B = r2.A)A | B -: | -: 3 | 1
db<>fiddle here