CODEWITHSUNDEEP
sqlt-sql
icecurtain
icecurtain

Reputation: 675

SQL reverse column value query

Need some Sql help? My Sql is not quite up to this yet. I have a table 

ColA    ColB    ColC
A       B       101
B       B       102
C       B       102
B       A       108

I need to be able identify the values in ColC where the rows that have the same entry in Col A and Col B (but reversed) i.e where Col B = Col A. But not like row three where Col B corresponding value has no match or row two where Col A = Col B.

The results set would be 

Col A      Col B     Col C
A          B         101
B          A         108

Upvotes: 2

Views: 2305

Answers (1)

podiluska
podiluska

Reputation: 51494

You can do a self join - ie: join the table to itself...

select t1.* , t1.ColC-t2.ColC
from yourtable t1
    inner join yourtable t2
       on t1.colA = t2.colB
       and t1.colB = t2.colA
where t1.colA <> t1.colB

Upvotes: 7

Related Questions