CODEWITHSUNDEEP
mysqlsql
Salo
Salo

Reputation: 31

Selecting data that does not have a reverse from table

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

Upvotes: 1

Views: 59

Answers (4)

wildplasser
wildplasser

Reputation: 44240

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
    );

Upvotes: 1

Luuk
Luuk

Reputation: 14920

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)

Upvotes: 1

Serg
Serg

Reputation: 22811

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

Upvotes: 2

CrafterKolyan
CrafterKolyan

Reputation: 1052

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

Upvotes: 2

Related Questions