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

Question

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.

can you post that as formatted text instead of all the HTML tags and explain what your end result should be? — S3S
– S3S, Commented Jul 31, 2018 at 21:27
Hi, I do not know how to format the text. You can find the attchement image for what am I looking for. Thanks. — Sandipan
– Sandipan, Commented Jul 31, 2018 at 21:35
Welcome to Stack Overflow. Your question doesn't include enough useful detail for us to help you. Please take a moment to read these two links, then consider editing your question if you're still looking for help. stackoverflow.com/help/how-to-ask and, regarding posting pictures, meta.stackoverflow.com/a/285557/5790584 — Eric Brandt
– Eric Brandt, Commented Jul 31, 2018 at 22:00
I haven't understand clearly what you are asking, but this might help you.. stackoverflow.com/questions/10195451/… — CR241
– CR241, Commented Jul 31, 2018 at 22:04
Sorry for the confusion, Please find the edited text if it helps. Thanks. — Sandipan
– Sandipan, Commented Aug 1, 2018 at 12:36

Adam · Accepted Answer · 2018-08-01 14:06:57Z

1

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;

edited Aug 1, 2018 at 14:06

answered Aug 1, 2018 at 1:00

Adam

4,6303 gold badges29 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sandipan Over a year ago

Thanks for you reply, but tblABC,tblDEF are dynamic, I do not know how many table names are there in col2 of Table-2. I need to get the data from all those table.

Adam Over a year ago

I've updated the answer to make the joins dynamic based on what's in Table2. You may need to add a DISTINCT to avoid duplicate joins?

Sandipan Over a year ago

Okay. Let me try that. Thanks.

Sandipan Over a year ago

Hi, I tried your approach, I had to do some tweaks but I got the direction, thanks for your help.

Adam Over a year ago

Gald to help. Please accept the answer if you thinks it's about right.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related