Selecting data that does not have a reverse from table-CodePudding

having trouble figuring this out. The question is basically a table with 2 integer datas, p1 and p2. So lets say p1=100, p2=101. There may or may not exist another row with the values p1=101,p2=100 ( the reverse). I have to find a query that will list ONLY THE ROWS THAT DO NOT HAVE THEIR REVERSE VERSION. Hopefully i was able to explain the question clearly but englando is hard... Any help is much appreciated.

EDIT: Forgot to mention, i must not use INNER,OUTER JOIN statements in the solution of this question.

An example Table: Looking at this table, i need to select only the 3rd row p1=106, p2=104.

   p1=101 , p2=103
   p1=103 , p2=101
   p1=106 , p2=104
   p1=108 , p2=105
   p1=105 , p2=108

CodePudding user response：

Something like this should work:

SELECT t1.p1, t1.p2
FROM tbl as t1
LEFT JOIN tbl as t2
ON t1.p1 = t2.p2 AND t1.p2 = t2.p1
WHERE t2.p1 IS NULL

Check it here: http://sqlfiddle.com/#!9/28b0af/6

CodePudding user response：

You can use least/greatest

select least(p1,p2) pl, greatest(p1,p2) pg
from tbl
group by least(p1,p2), greatest(p1,p2)
having count(*) = 1

CodePudding user response：

This will work too (and no JOINs used):

select t1.p1,t1.p2
from tbl t1
where not exists(select p2,p1 from tbl where p2=t1.p1 and p1=t1.p2)

CodePudding user response：

NOT EXISTS(...) is the most intuitive solution:

SELECT *
FROM thetable tt
WHERE NOT EXISTS (
    SELECT * FROM thetable nx
    WHERE nx.p1 = tt.p2
    AND nx.p2 = tt.p1
    );