CODEWITHSUNDEEP
sql-serverjoindynamic
Sandipan
Sandipan

Reputation: 27

Reference table name column in another table in join - SQL Server

I have following condition :

Table-1
col1   col2    col3
1         40     100 (identity column value for tblABC)
2         41     101 (identity column value for tblDEF)

Table-2
col1       col2        col3
40        tblABC     tblABCPrimaryKey
41        tblDEF     tblDEFPrimaryKey

========= Different tables ==============
tblABC
tblPrimaryKeyId    col2
100                        VALUE

tblDEF
tblPrimaryKeyId    col2
101                       VALUE

I need to get below column in join
         select Table-1.col1,
                   Table-2.col2,
                   [ tblABC.col2 OR tblDEF.col2 and so on depending on the table]
from Table-1
INNER JOIN (join goes here)

I want to get
col1 from Table-1,
col2 from Table-2 where col2 of Table-1 matches col1 of Table-2,
col2 from (TABLES IN COL2 of Table-2 where col3 of Table-2 matches column in those tables in col-2 [Different tables])

Please help.

Upvotes: 1

Views: 2049

Answers (1)

Adam
Adam

Reputation: 4580

DECLARE @sql NVARCHAR(MAX) = '
SELECT Table1.col1
    , Table2.col2,
    , COALESCE(';

SELECT
    @sql = @sql + #Table2.col2 + '.col2, '
FROM #Table2;

SET @sql = @sql + ' NULL)
INNER JOIN Table2
    ON Table1.col2 = Table2.col1';

SELECT
    @sql = @sql + '
LEFT JOIN ' + col2 + ' 
    ON Table2.col2 = ''' + col2 + ''' 
    AND Table1.col3 = ' + col2 + '.col1'
FROM #Table2

EXEC sys.sp_executesql @sql;

Upvotes: 1

Related Questions