How to simplify complex JOIN ON condition

I have a working but very ugly, redundant join in Transact-SQL. The ON condition is of the form

ON ((A = 1 AND B) OR (A = 0 AND C))

where A, B, and C are themselves complex conditions. In other words, if A is true, B applies, and if A is false, C applies. It seemed like IIF should work so that A can be evaluated only once, but IIF cannot resolve to boolean. Maybe the optimizer only evaluates A once but the code is very ugly. How can I simplify to eliminate the redundant A evaluation in the condition? Thanks for any help.

Please supply sample data in the form of a create table script with insert of sample data script, what you've tried and what result you would like.

The trick to problems like this is usually to move the calculation for "A" into a CROSS APPLY, then you can refer to the column without forcing re-evaluation of the expression (or being forced to type it out multiple times)

1 Like

CASE should evaluate it only once, although it may not help performance of the join, if that is your ultimate goal:

ON 1 = CASE A WHEN 1 THEN B/make B return 1 when true/ WHEN 0 THEN C/1 if true/ END

1 Like
In detail, the solution I settled on took the form
	ON 1 = CASE A
			IIF (B, 1, 0)
			IIF (C, 1, 0)
Thank you.  It's been a learning experience, which is always good.

My nicely-indented code came out left-aligned. Sorry about that. How do you post while keeping indents?