SQL how to compare two fields?

Could I get a little help/direction on this? I am new to SQL and having a difficult time finding what direction to go in here. I have 2 fields I need to compare and supply an answer based on what is in those fields. So field 1 & 2 could have data = a,b,c or d. Based on the comparison the answer could be 0 through 4.

• IF a & a THEN 0
• IF a & b THEN 1
• IF a or b & c THEN 2
• IF c & c THEN 3
• IF a or b or c & d THEN 4

How would I best approach this in SQL?

Any help would be appreciated.

Answer

You want a case expression. I think the logic you are trying to express is:

(case when field1 = 'a' and field2 = 'a' then 0
      when field1 = 'a' and field2 = 'b' then 1
      when field1 in ('a', 'b') and field2 = 'c' then 2
      when field1 = 'c' and field2 = 'c' then 3
      else 4
 end)