none
How to combine multiple tables based on an ID that it not available in all of them, RRS feed

  • Question

  • How to combine multiple tables based on an ID that it not available in all of them,

     For example, in the screenshot below, I wanted to combine T1, T2 and T3 to end up with T4. Which SQL code can perform this?

     Thank you

     Best

     Jamal


    jamal

    Thursday, July 4, 2019 6:02 PM

Answers

  • Something like this:

    SELECT
     COALESCE(T1.F1, T2.S1, T3.H1) AS R1,
     T1.F2 AS R2,
     T2.S2 AS R3,
     T3.H2 AS R4
    FROM T1
    FULL OUTER JOIN T2 ON T2.S1 = T1.F1
    FULL OUTER JOIN T3 ON T3.H1 = T1.F1


    A Fan of SSIS, SSRS and SSAS

    Thursday, July 4, 2019 7:46 PM

All replies

  • Post DDLs of those tables and some sample data and the outputs you expect.

    A Fan of SSIS, SSRS and SSAS

    Thursday, July 4, 2019 7:33 PM
  • Something like this:

    SELECT
     COALESCE(T1.F1, T2.S1, T3.H1) AS R1,
     T1.F2 AS R2,
     T2.S2 AS R3,
     T3.H2 AS R4
    FROM T1
    FULL OUTER JOIN T2 ON T2.S1 = T1.F1
    FULL OUTER JOIN T3 ON T3.H1 = T1.F1


    A Fan of SSIS, SSRS and SSAS

    Thursday, July 4, 2019 7:46 PM
  • Thanks for the answer

    the T1, T2, T3 are given in xlsx file as sheets

    the expected result is saved in sheet T4

    the data is here:

    http://www.mediafire.com/file/hvrbk3fzvq5vrnw/Combine.xlsx/file


    jamal

    Thursday, July 4, 2019 8:42 PM
  • Many thanks for the help. it works like a charm


    jamal

    Thursday, July 4, 2019 9:14 PM