Is it possible to inner join a different table based on a sproc parameter?-CodePudding

I'm in charge of a legacy project and I was looking at simplifying or streamlining a bit.

What I want to do is something like:

declare @n int = 1
select * from table_a a
case when @n = 1 then inner join table_b b on b.x = a.x
else inner join table_c c a.x = c.x
end

The main reason I'm hoping this is possible is because I'm trying to get rid of a lot of repetition in the current sproc. Every time something changes, we have to make sure that each section is updated appropriately.

Currently it looks something like:

if @a = 1 begin
select (long list of columns but not all) from table_A
inner join table_2 a on table_A.a = table_2.a
left join table_B on table_A.c = table_B.c
where (several conditions here)
end
else if @= 2 begin
select (same long list of columns) from table_A
inner join table_3 a on table_A.a = table_3.a
left join table_B on table_A.c = table_B.c
where (same several conditions)
end
else
select (same list again) from table_A
inner join table_4 a on table_A.a = table_4.a
left join table_B on table_A.c = table_B.c
where (same conditions again)
end

In the example, tables 2, 3, and 4 are essentially identical as far as columns are concerned but are used to separate groups.

If it's not possible, I'm thinking I could combine all three of those tables into a single table and add a new column denoting the group and then inner join on that column along with the other one. I just figured that I'd rather not move data around if I didn't have to.

CodePudding user response：

A variation of the following technique can be used, I'm using an outer join with null check.

declare @myVar int
set @myVar = 1

select 
    t1.id, 
    t1.nm,
    coalesce(t2.nm, t3.nm) valu
from 
        table1 t1 
    left outer join 
        table2 t2 on (t1.id = t2.id and @myVar = 1)
    left outer join 
        table3 t3 on (t1.id = t3.id and @myVar = 2)
 where coalesce(t2.nm, t3.nm) is not null

The basic made-up schema in use:

select * 
into table1
from 
(select 1 id, 'a' nm union
select 2, 'b' union
select 3, 'c' union
select 7, 'j') a


select * 
into table2
from 
(select 1 id, 'a1' nm union
select 2, 'b1' union
select 3, 'c1' union
select 4, 'd1' ) a


select * 
into table3
from 
(select 1 id, 'a2' nm union
select 2, 'b2' union
select 3, 'c2' union
select 4, 'd2' union
select 5, 'e2' ) a

Does this work for you?

CodePudding user response：

You can use an CROSS APPLY(...UNION ALL...) to effectively implement a conditional join to your middle table.

select (long list of columns but not all)
from table_A
cross apply (
    select t2.c from table_2 t2 where t2.a = table_A.a and @a = 1
    union all
    select t3.c from table_3 t3 where t3.a = table_A.a and @a = 2
    union all
    select t4.c from table_4 t4 where t4.a = table_A.a and @a not in (1,2)
) X
left join table_B on X.c = table_B.c
where (several conditions here)

This should even run quite efficiently, assuming that you have indexes on table_2.a, table_3.a, table_4.a, and table_B.c.