CODEWITHSUNDEEP
sqlsql-servert-sql
Lacrymae
Lacrymae

Reputation: 157

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.

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

Upvotes: 3

Views: 1698

Answers (2)

forpas
forpas

Reputation: 164184

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

Upvotes: 1

Dale K
Dale K

Reputation: 27426

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

Upvotes: 4

Related Questions