Mutual friend column, select a pair only once

Question

I have a likes table where ID1 likes ID2

 ID1 ID2 1025 1101 1247 1468 1316 1304 1501 1934 1641 1468 1689 1709 1709 1689 1782 1709 1911 1247 1934 1501 

so I choose those who like each other, for example

 1501 1934 1934 1501 

But I want to select each pair only once, and I cannot do it. Can someone point me in the right direction? its part of a larger request, but this part that I cannot do.

thanks

Answer 1

To get a great list of everyone you like each other, you can use

 SELECT ID1, ID2 FROM likes L1 WHERE ID1 > ID2 AND EXISTS(SELECT * FROM likes L2 WHERE L1.ID1 = L2.ID2 AND L1.ID2 = L2.ID1) 

Answer 2

 select L1.ID1, L1.ID2 from likes L1 where exists (select 1 from likes L2 where L1.ID1 = L2.ID2 and L1.ID2 = L2.ID1) 

Answer 3

Correct data:

 SELECT ID1, ID2 FROM likes WHERE ID1 < ID2 UNION SELECT ID2 AS ID1, ID1 AS ID2 FROM likes WHERE ID1 > ID2; 

... then fix the leak by adding data integrity constraints to the table, for example.

 CREATE TABLE likes ( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, CHECK (ID1 < ID2), UNIQUE (ID1, ID2) ); 

Answer 4

 SELECT L1.ID1, L1.ID2 FROM Likes L1, Likes L2 WHERE L1.ID1=L2.ID2 AND L1.ID2=L2.ID1 AND L1.ID1>L1.ID2