Geek Answers Handbook

Define a table to join based on a condition

I'm not sure that I missed the search correctly, but I could not get the exact question, like mine

It is something similar, but not exact.

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

Actually, I want to decide which table to join based on the parameter passed to the stored procedure, case when did not work

View

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

Table 2 and Table 3 have the same structure.

+6
source share
2 answers

I can recall several 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 latter, you will need to check the plan that the optimizer creates to see how much it matches performance.

It seems like you're looking for code reuse, so maybe option 2 is your best. T-SQL does not actually lend itself to such polymorphism, but you can use workarounds in some cases.

Taking a step back, you need to ask one question: if the tables have the same structure, maybe you should just use one table? You can then use @Parameter to simply filter the lines you need, rather than trying to create dynamic queries.

It is also worth noting that in such situations, you can handle code generation at the application level using ORM, etc.

+7
source

Try the following:

 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
+1
source

More articles:


All Articles