SQL apply join dynamically based on column names mentioned in other table

Question

I have table for example temp

Id Col1 Col2 Col3
1  1     2    3

and I have another table joininfo

Id SourceKey Table TargetKey
1  Col1       A     ColA
2  Col2       B     ColB
3  Col3       C     ColC

I want to generate a query which will add inner join clause dynamically and will look like this

SELECT * FROM temp 
INNER JOIN A ON Col1=ColA 
INNER JOIN B ON Col2=ColB 
INNER JOIN C ON Col3=ColC

Any help?

I believe it will be generated by dynamic query and with the help of CTE — Billz, Apr 12 '21 at 15:47
You will need to build the query dynamically and execute it with `sp_executesql` — Stu, Apr 12 '21 at 15:50
It is usually a bad idea to store column and table names in tables - unless you are building a DBMS yourself ;-) So, the *real* solution to the problem may be another data model. — Thorsten Kettner, Apr 13 '21 at 07:03

Joel Coehoorn · Answer 1 · 2021-04-12T16:02:19.650

1

Can't do it.

SQL needs to know this stuff at query compile time, before looking at any data, so it can validate security and check for possible indexes. The only query element comes close to looking at data as if it were a column after query compile time is the PIVOT keyword.

Otherwise, you're down to a CASE expression listing every possible set of column compares, or writing dynamic SQL over multiple steps where you first execute a query to find what columns/joins you need, use those results to build a new query string, and then execute the string you just made.

edited Apr 12 '21 at 16:02

answered Apr 12 '21 at 15:48

Joel Coehoorn

399,467
113
570
794

I believe it will be generated by dynamic query and with the help of CTE – Billz Apr 12 '21 at 15:49
If you want to try a dynamic query, you can do that... but **try the dynamic query** first. Get as far as you can down that road and then post a new question when you get stuck. CTE has nothing to do with it and won't help you here. – Joel Coehoorn Apr 12 '21 at 15:49
For dynamic query, I am confused in looping all the rows – Billz Apr 12 '21 at 15:56
Okay... so post that into the question, including the code you used to make the attempt. – Joel Coehoorn Apr 12 '21 at 15:58
*"I am confused in looping all the rows"* ideally, you shouldn't be looping in your SQL anyway, @Billz . SQL is a set based language, so performs best at set based operations and poorly at iterative ones. Not sure, as well, why this has been downvoted. – Thom A Apr 12 '21 at 16:00

Raseena Abdul · Answer 2 · 2021-04-12T19:55:00.573

0

As per the comment from Charlieface updating the answer using string_agg

    declare 
    @dsql nvarchar(max)
   
select @dsql = 'select * from temp ' + string_agg(' join '+ Table + ' on ' + c ,' ') 
from (
select Table , string_agg ( SourceKey + ' = ' + TargetKey,' and ') c
from table1
group by Table ) t
     
        select @dsql
        EXECUTE sp_executesql @dsql

edited Apr 12 '21 at 19:55

answered Apr 12 '21 at 16:34

Raseena Abdul

516
3
6

1 issue is here column name and join table rows are dynamic, it can be more than 3 as well – Billz Apr 12 '21 at 16:45
1

This is also **dangerously** open to injection. – Thom A Apr 12 '21 at 16:57
@Larnu Yes I agree. Since the table name itself is dynamic, I haven't been successful on finding an alternative solution. The application need to be careful on how/who can update joininfo table. This answer is also helpful :- https://stackoverflow.com/questions/20678725/how-to-set-table-name-in-dynamic-sql-query/29082755#29082755 – Raseena Abdul Apr 12 '21 at 17:11
@Charlieface It didn't occur to me. I updated the answer. Thank you. – Raseena Abdul Apr 12 '21 at 19:55

score -1 · Accepted Answer · answered Apr 12 '21 at 20:11

You can build a dynamic SQL query quite neatly by using string aggregation:

Try to keep clear about which bits are static and which dynamic. And test the generated code by using PRINT @sql

DECLARE @sql nvarchar(max) = N'

SELECT *
FROM temp s
' +
(
    SELECT STRING_AGG(CAST(
N'JOIN ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name) + N' AS T' + CAST(t.object_id AS nvarchar(10)) + N' 
  ON s.' + QUOTENAME(j.SourceKey) + N' = T' + CAST(t.object_id AS nvarchar(10)) + N'.' + QUOTENAME(c.name)
    AS nvarchar(max)), N' 
') WITHIN GROUP (ORDER BY j.Id)
    FROM sys.tables t
    JOIN sys.columns c ON c.object_id = t.object_id
    JOIN joininfo j ON OBJECT_ID(j.[Table]) = t.object_id
        AND j.TargetKey = c.name
);

PRINT @sql;   -- for testing

EXECUTE sp_executesql @sql;

If your version of SQL Server does not support STRING_AGG you can use FOR XML PATH('') instead.

SQL apply join dynamically based on column names mentioned in other table

3 Answers3