Conditional (case statement) Join Criteria

Database

Conditional (case statement) Join Criteria

I'm relatively new to database development in general and I've got a question related to the use of CASE statements when joining tables. Here's the situation I'm faced with.

I have a table (T1) that looks like this:
key1 key2 key3 key4
aaa zz 1 2
aaa zz 2 3
bbb yy 2 3
ccc xx 3 4

And another table (T2) that looks like this:
key1 key2 key3 key4
aaa zz 1 2
bbb yy ^ ^
ccc xx 3 ^

As you can see the keys in T2 are not always the same as T1 (don't ask why). But I need to use as many of the keys as I can in the join. What I coded is this:
ON
T1.key1 = T2.key1
and T1.key2 = T2.key2
and T1.key3 = case when T2.key3 = '^'
then T1.key3
else T2.key3
end
and T1.key4 = case when T2.key4 = '^'
then T1.key4
else T2.key4
end
It seems to work (not 100% confident), but I'm not quite sure why. Any help is appreciated.
Tags (3)
1 REPLY

Re: Conditional (case statement) Join Criteria

Can you post some example for the same in which you facing issue.