CODEWITHSUNDEEP
sqlsql-serverjoinsql-updatecase
TigSh
TigSh

Reputation: 645

Join on 2 different conditions based on CASE expression

I want to write an update query which joins 2 tables based on different Case conditions

Update Table1
Set ID = Case when T1.value1 < 0
          then T2.ID ------Join on value1 between T2.valueTo and T2.ValueFrom
               when T1.value2 > 0
          then T2.ID -------Join on value2 between T2.valueTo and T2.ValueFrom
 from Table1
 Join Table2 t2 on value1 between T2.valueTo and T2.ValueFrom OR value2 between T2.valueTo and T2.ValueFrom

The above query doesn't work. Is there a way I can write which join should work based on the case expression.

Upvotes: 1

Views: 39

Answers (1)

Mureinik
Mureinik

Reputation: 311768

You could use two joins, one for each condition:

Update Table1
Set ID = Case when T1.value1 < 0
          then T2.ID ------Join on value1 between T2.valueTo and T2.ValueFrom
               when T1.value2 > 0
          then T3.ID -------Join on value2 between T2.valueTo and T2.ValueFrom
 from Table1
 Join Table2 t2 on value1 between T2.valueTo and T2.ValueFrom
 Join Table2 t2 on value2 between T3.valueTo and T3.ValueFrom

Upvotes: 1

Related Questions