CODEWITHSUNDEEP
sql
matei basar
matei basar

Reputation: 43

SQL QUERY FOR PAIRS

I have the following values in my table

A B
1 2
2 3 
4 5 
2 1 
5 6
7 6
6 5

what a sql query in order to find the results which have a pair so this is the output is

1 2 
2 1 
5 6 
6 5

Upvotes: 0

Views: 783

Answers (4)

Neha Jha
Neha Jha

Reputation: 291

Doing an Inner join will help you in this case:

SELECT t.col1, t.col2 
FROM Tablename t
INNER JOIN Tablename t2
ON t.col1 = t2.col2 AND t.col2 = t2.col1

This will give you the required result:
Sample Output :

Innings Totalplayer
2 1
1 2
6 5
5 6

View on DB Fiddle

Upvotes: 0

Ilya Oblomo
Ilya Oblomo

Reputation: 104

Seems like there're already great solutions:

SELECT t.A, t.B FROM table AS t1 INNER JOIN table AS t2 ON t1.A = t2.B AND t1.B = t2.A

Upvotes: 1

Raseena Abdul
Raseena Abdul

Reputation: 536

This is another way to do it. With Self join

SELECT t1.* 
FROM table1 t1
table1 t2 ON t1.a = t2.b AND t2.a = t1.b

Upvotes: 0

Sergey
Sergey

Reputation: 5217

 WITH CTE(A,B) AS
 (

  SELECT 1, 2 UNION ALL 
  SELECT 2, 3 UNION ALL 
  SELECT 4, 5 UNION ALL 
  SELECT 2 ,1 UNION ALL 
  SELECT 5, 6 UNION ALL 
  SELECT 7, 6 UNION ALL 
  SELECT 6 ,5 
 )
 SELECT C.A,C.B
    FROM CTE AS C
     JOIN CTE AS C2 ON C.A=C2.B AND C.B=C2.A

Upvotes: 0

Related Questions