none
How to merge multiple tables in SQL query?

    Question

  • I'm new to SQL. I have 3 queries created deparately and finding a way to merge them together.

    Table 1:
    ID   Name   Total_X
    1     abc       22
    12   bcd       5
    6     tfg        11

    Table 2:
    ID   Name   Total_y
    4    otg         50
    1    abc        13
    9    eft         21


    Table 3:
    ID   Name   Total_Z
    10   utt         4
    6     tfg        45
    5     ddf       19

     

    After the merge of tables should look like this, any help?
    Merged table:
    ID   Name   Total_X   Total_y    Total_z
    1     abc         22        13         Null
    4     otg         Null      50         Null
    5     ddf         Null      Null       19
    6     tfg          11        Null       45
    9     eft         Null      21         Null
    10   utt         Null      Null       4
    12   bcd        5         Null       Null

    Friday, December 11, 2009 11:07 AM

Answers

  • CREATE TABLE #1
    (ID INT, NAME CHAR(3), Total_X INT)
    CREATE TABLE #2
    (ID INT, NAME CHAR(3), Total_Y INT)
    CREATE TABLE #3
    (ID INT, NAME CHAR(3), Total_Z INT)
    
    INSERT INTO #1 SELECT 1, 'abc', 22
    INSERT INTO #1 SELECT 12, 'bcd', 5
    INSERT INTO #1 SELECT 6, 'tfg', 11
    
    INSERT INTO #2 SELECT 4, 'otg', 50
    INSERT INTO #2 SELECT 1, 'abc', 13
    INSERT INTO #2 SELECT 9, 'eft', 21
    
    INSERT INTO #3 SELECT 10, 'utt', 4
    INSERT INTO #3 SELECT 6, 'tfg', 45
    INSERT INTO #3 SELECT 5, 'ddf', 19
    
    SELECT COALESCE(a.ID, b.ID, c.ID), 
    		COALESCE(a.NAME, b.NAME, c.NAME),
    		Total_X,
    		Total_Y,
    		Total_Z
    FROM #1 a
    	FULL OUTER JOIN #2 b
    		ON a.ID = b.ID
    	FULL OUTER JOIN #3 c
    		ON a.ID = c.ID
    

    every day is a school day
    • Proposed as answer by Sridhar Boyapati Friday, December 11, 2009 11:39 AM
    • Marked as answer by rhst11 Saturday, December 12, 2009 2:58 AM
    Friday, December 11, 2009 11:19 AM

All replies

  • Hi

    You can use the query as below

    select ID, Name , Total_X, NULL as Total_Y, NULL Total_Z From Table1

    union

    select ID, Name , NULL as Total_X, Total_Y, NULL Total_Z From Table2

    union

    select ID, Name , NULL as Total_X, NULL as Total_Y, Total_Z From Table3

     Order by ID



    Thanks


    Gnanasekar Babu Note: Please click on the vote button if the answer helps you
    Friday, December 11, 2009 11:15 AM
  • CREATE TABLE #1
    (ID INT, NAME CHAR(3), Total_X INT)
    CREATE TABLE #2
    (ID INT, NAME CHAR(3), Total_Y INT)
    CREATE TABLE #3
    (ID INT, NAME CHAR(3), Total_Z INT)
    
    INSERT INTO #1 SELECT 1, 'abc', 22
    INSERT INTO #1 SELECT 12, 'bcd', 5
    INSERT INTO #1 SELECT 6, 'tfg', 11
    
    INSERT INTO #2 SELECT 4, 'otg', 50
    INSERT INTO #2 SELECT 1, 'abc', 13
    INSERT INTO #2 SELECT 9, 'eft', 21
    
    INSERT INTO #3 SELECT 10, 'utt', 4
    INSERT INTO #3 SELECT 6, 'tfg', 45
    INSERT INTO #3 SELECT 5, 'ddf', 19
    
    SELECT COALESCE(a.ID, b.ID, c.ID), 
    		COALESCE(a.NAME, b.NAME, c.NAME),
    		Total_X,
    		Total_Y,
    		Total_Z
    FROM #1 a
    	FULL OUTER JOIN #2 b
    		ON a.ID = b.ID
    	FULL OUTER JOIN #3 c
    		ON a.ID = c.ID
    

    every day is a school day
    • Proposed as answer by Sridhar Boyapati Friday, December 11, 2009 11:39 AM
    • Marked as answer by rhst11 Saturday, December 12, 2009 2:58 AM
    Friday, December 11, 2009 11:19 AM
  • Hi Gnanasekar, your query only produce this, doesn't match my expectation
    Any other suggestion?

    ID Name Total_X Total_Y Total_Z
    1 abc NULL 13 NULL
    1 abc 22 NULL NULL
    4 otg NULL 50 NULL
    5 ddf NULL NULL 19
    6 tfg NULL NULL 45
    6 tfg 11 NULL NULL
    9 eft NULL 21 NULL
    10 utt NULL NULL 4
    12 bcd 5 NULL NULL
    Friday, December 11, 2009 11:37 AM
  • U r right Rich.....

    Ur query increases the Performance than Gnansekhar babu.......
    Friday, December 11, 2009 11:41 AM
  • Thanks richbrownesq.

    Your method is really helpful. It works well.

    Saturday, December 12, 2009 2:57 AM