Conditional join in SQL Server dependent on other table values

I need make a decision which table should be use in join statement depend on values in another table

I tried using CASE and COALESCE but can’t achieve any success.

• TableA has A and B and C and many other columns
• TableB has ID and NAME columns
• TableC has ID and NAME columns

My select statement is;

Select A.D, A.E, A.F From TableA A

If A.E = 1 then the following join should be used

left outer join TableB B ON A.B = B.ID

and B.NAME should be returned in the select statement

If A.E = 2 then the following join should be used

left outer join TableC C ON A.B = C.ID

and C.NAME should be returned in the select statement

Answer

Just add your conditions to the joins, and then use a case statement to pull the correct field to your result set e.g.

select A.D, A.E, A.F
  , case when B.[Name] is not null then B.[Name] else C.[Name] end [Name]
from TableA A
left outer join TableB B ON A.B = B.ID and A.E = 1
left outer join TableC C ON A.B = C.ID and A.E = 2