Conditional join in SQL Server dependent on other table values

Question

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

Sample data, and expected results, along with your attempt(s) will be really helpful here. — Thom A
– Thom A ♦, Commented Aug 5, 2019 at 21:41

Dale K · Accepted Answer · 2019-08-05 21:41:15Z

4

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

answered Aug 5, 2019 at 21:41

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AlwaysLearning Over a year ago

You could also use coalesce() to simplify the case statement since there would never be a case when B.* and C.* are both not null.

Lacrymae Over a year ago

Thank you for your answer. Its working very well. Saving my day :)

forpas · Accepted Answer · 2019-08-05 21:48:51Z

1

Join tablea with the union of tableb with an extra column with value 1 and tablec with an extra column with value 2 and apply the conditions in the ON clause:

select 
  a.D, a.E, a.F, u.NAME
from tablea a
left join (
  select *, 1 col from tableb
  union all
  select *, 2 col from tablec
) u on a.B = u.id and a.E = u.col

answered Aug 5, 2019 at 21:48

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

Conditional join in SQL Server dependent on other table values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related