Determine table to join based on the condition

Question

I am not sure if I've missed to search properly, but I couldn't get the exact question as mine

This is something similar but not exact

https://stackoverflow.com/questions/11533636/determining-which-table-to-join-to

Actually I want to decide with which table to join based on the parameter passed to stored procedure, case when didn't work

Kind of

select * from Table1 
       left join (case when @Parameter<>NULL Then Table2 else Table3 end) final
       on Table1.Col1 = final.Col1

Table2 and Table3 has same structure

Answer 1

I can think of a few of options:

1: IF ... ELSE

IF @Parameter IS NULL

  SELECT *
  FROM T1
    INNER JOIN T2 ON T1.ID = T2.ID

ELSE

  SELECT *
  FROM T1
    INNER JOIN T3 ON T1.ID = T3.ID

2: Dynamic T-SQL

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'SELECT *
  FROM T1
    INNER JOIN ' + CASE WHEN @Parameter IS NULL THEN N'T2 t' ELSE N'T3 t' END
  + N' ON T1.ID = t.ID';

EXEC sp_executesql @SQL;

3: UNION ALL and subquery.

  SELECT *
  FROM T1
    INNER JOIN
    (
      SELECT *
      FROM T2
      WHERE @Parameter IS NULL
      UNION ALL
      SELECT *
      FROM T3
      WHERE @Parameter IS NOT NULL
    ) t ON T1.ID = t.ID

For this last one you'd have to check the plan the optimiser creates to see if it's OK performance wise.

It seems like you're looking for code reuse, so maybe option 2 is your best one. T-SQL doesn't really lend itself to this sort of polymorphism but you can use workarounds in some cases.

Taking a step back, one question to ask is, if the tables have the same structure, maybe you should just use one table? Then you could use @Parameter to just filter the rows you require instead of trying to create dynamic queries.

It's also worth noting that in situations like this you could handle the code-generation at the application layer with ORMs and the like.

Answer 2

Try this:

select *
from (
  select data.*,
    case when selection=1 then t1.ExtraField
         when selection=2 then t2.ExtraField
         when selection=3 then t3.ExtraField
         else NULL
    end as ExtraField
  from data
  left join t1 on t1.key = data.key and selection=1
  left join t2 on t2.key = data.key and selection=2
  left join t3 on t3.key = data.key and selection=3
) T
where ExtraField is not NULL

Answer 3

Done with this

SELECT PA.`module_id` as usertype, PA.module_pk_id,   (
            CASE PA.`module_id`
                WHEN '0'
                THEN pea.`name`
                ELSE CONCAT(u.`first_name`, " ",u.`last_name`)
            END
        ) as name
    
    FROM `placements_approvers` PA 
        LEFT JOIN
           
                placements_external_approvers pea  
             
            ON
              PA.module_pk_id = pea.placement_external_approver_id AND  PA.`module_id` = 0
               
            LEFT JOIN
           
                users u    
            ON
              PA.module_pk_id = u.user_id AND PA.`module_id` != 0
            WHERE PA.placement_id = '494' AND PA.approver_type = 1