CODEWITHSUNDEEP
sqloracle-database
Nemo
Nemo

Reputation: 1079

SQL : Getting cross matching columns

Below is the scenario,

Table A

---------------------
| Col 1     | Col 2  
---------------------
| 1         | 2      
---------------------
| 3         | 4           
---------------------
| 2         | 1             
---------------------
| 4         | 3           
---------------------

Here in col 1 there is some data '1' and in col 2 there is some data '2'. In the same table there is another row where these values are interchanged. How can i get the output as below using SQL query

Output

--------------------------
| Col 1     | Col 2      |
--------------------------
| 1         | 2          |
--------------------------
| 3         | 4          |
--------------------------

Can any one help me to write the SQL query for this?

Upvotes: 2

Views: 181

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

One option would be

SELECT a.col1, a.col2
  FROM tableA a
 WHERE NOT EXISTS( SELECT 1
                     FROM tableA b
                    WHERE a.col1 = b.col2
                      AND a.col2 = b.col1
                      AND a.col1 > b.col1 );

which produces the output you want

SQL> ed
Wrote file afiedt.buf

  1  with tableA as (
  2    select 1 col1, 2 col2 from dual union all
  3    select 3, 4 from dual union all
  4    select 2, 1 from dual union all
  5    select 4, 3 from dual
  6  )
  7      SELECT a.col1, a.col2
  8        FROM tableA a
  9       WHERE NOT EXISTS( SELECT 1
 10                           FROM tableA b
 11                          WHERE a.col1 = b.col2
 12                            AND a.col2 = b.col1
 13*                           AND a.col1 > b.col1 )
SQL> /

      COL1       COL2
---------- ----------
         1          2
         3          4

Upvotes: 4

Related Questions