user3538475 user3538475 - 7 months ago 10
SQL Question

How to compare three column in sql

I want compare 3 column value which comparison value from Col2 in table.

Col1 Col2 Col3


12 < 25 TRUE


25 > 20 TRUE


15 = 25 FALSE


SELECT (case when Col2 = '<' then Col1 < Col3 else Col1 end)


Can anyone help me ?
Advanced Thanks.

Answer

You can try using case, like this:

 case 
   when Col2 = '<' then
     Col1 < Col3
   when Col2 = '>' then
     Col1 > Col3
   when Col2 = '=' then
     Col1 = Col3
 end case

the query could be

 select case 
          when col2 = '=' then
            case when (col1 = col3) then 1 else 0 end
          when col2 = '>' then
            case when (col1 > col3) then 1 else 0 end
          when col2 = '<' then
            case when (col1 < col3) then 1 else 0 end
          else
            0  
        end 
   from MyTable

another possibily is

  (Col2 = '<' and (Col1 < Col3)) or
  (Col2 = '>' and (Col1 > Col3)) or
  (Col2 = '=' and (Col1 = Col3))