How to combine case statement with left join in sql server?

0
votes

I have two tables that I will have to left join them conditionally. The definition of the tables are tbl1 (col1, col2, col3, …, coln), tbl2 (col1, col2, col3), col1 from both tables is defined as char(2), col2 is defined as char(4). For example tbl1:

col1    col2    col3    …    coln
01      0010
02      0014
10      1235
29      2566
38      1235
8A      1232

tbl2: (x means it is one letter and could be anything from 0-9 or a-z)

col1    col2    col3
0x       NULL
1x       NULL
2x       NULL
...
9x       NULL
13       1234
31       1890
32       1342
3a       1232
...

I need join those two tables based on the value of the col1 and col2 from tbl2. Following is my query

select * 
from tbl1 left join tbl2
on case when tbl2.col1 like '[0-9]x' then left(tbl1.col1, 1)=left(tbl2.col1, 1)
else tbl1.col1=tbl2.col1 and tbl1.col2=isnull(tbl2.col2, tbl1.col2) end

I got an error "Incorrect syntax near '='". Can anyone tell me why? In addition, I also want to put cases such as (3a, 13xx, …) , (3a, 123x, …) or (3a, 1xxx, …) in consideration. Any anyone shed some light?

3
sql-serverjoin

3 Answers

1
votes

A case statement evaluates to a value, while the on clause expects a condition.

You can turn a value into a condition by comparing it with 1, for example:

case 
when tbl2.col1 like '[0-9]x' and left(tbl1.col1, 1) = left(tbl2.col1, 1) then 1
when tbl1.col1 = tbl2.col1 and tbl1.col2 = isnull(tbl2.col2, tbl1.col2) then 1
end = 1
0
votes

Instead of a CASE statement, you can just use multiple sets of criteria with OR:

select * 
from tbl1 
left join tbl2
on   (tbl2.col1 like '[0-9]x' AND left(tbl1.col1, 1) = left(tbl2.col1, 1))
  OR (tbl1.col1=tbl2.col1 AND tbl1.col2 = isnull(tbl2.col2, tbl1.col2))
0
votes

You could use a cte to build results, something like this: