tags:

views:

43

answers:

2

I've got two tables:

TableA
    Col1
    Col2
TableB
    Col3
    Col4

I want to join them together:

SELECT * from TableA join TableB ON (...)

Now, in place of ... I need to write an expression that evaluates to:

  1. If Col3 is not null, then true iif Col1==Col3; otherwise
  2. If Col3 is null, then true iif Col2==Col4

What would be the most elegant way to do this?

+4  A: 
ON (Col1=Col3 OR (Col3 IS NULL AND Col2=Col4))

should do the trick (if Col3 is null, Col1=Col3 cannot evalutate to TRUE)

ammoQ
Your OR and AND conditions might be confusing.
Randolph Potter
Nice, thank you! I thought that there should be an easier way than just a bunch of deeply nested case-when statements! :)
Vilx-
@Randolph Potter - makes sense to me. I'll just add parenthesis around the AND condition.
Vilx-
Randoph: AND before OR... for some people, it's as clear as * before +; other people prefere parenthesis for clearness.
ammoQ
anyway, by popular demand, parenthesis added
ammoQ
To be honest, I can never remember which comes first, but the idea behind this solution was so clear that there was no need to clarify. :)
Vilx-
A: 

Try this:

SELECT * 
FROM TableA 
JOIN TableB 
   ON Col1 = Col3
      OR (Col3 IS NULL AND Col2 = Col4)
ck