1

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Sandipan
  • 27
  • 8
  • 1
    can you post that as formatted text instead of all the HTML tags and explain what your end result should be? – S3S Jul 31 '18 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 Jul 31 '18 at 21:35
  • 1
    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. https://stackoverflow.com/help/how-to-ask and, regarding posting pictures, https://meta.stackoverflow.com/a/285557/5790584 – Eric Brandt Jul 31 '18 at 22:00
  • 1
    I haven't understand clearly what you are asking, but this might help you.. https://stackoverflow.com/questions/10195451/sql-inner-join-with-3-tables – CR241 Jul 31 '18 at 22:04
  • Sorry for the confusion, Please find the edited text if it helps. Thanks. – Sandipan Aug 01 '18 at 12:36

1 Answers1

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;
Adam
  • 4,180
  • 2
  • 27
  • 31
  • 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. – Sandipan Aug 01 '18 at 12:51
  • 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? – Adam Aug 01 '18 at 14:07
  • Okay. Let me try that. Thanks. – Sandipan Aug 01 '18 at 15:07
  • Hi, I tried your approach, I had to do some tweaks but I got the direction, thanks for your help. – Sandipan Aug 01 '18 at 15:52
  • Gald to help. Please accept the answer if you thinks it's about right. – Adam Aug 01 '18 at 17:50