How to merge 4 tables into one.

Samuel 21 Reputation points
2022-09-04T20:53:18.937+00:00

Hi everyone.
I have a scenario where I have 4 tables. In all these tables 1 column is common which is AddresseId.
Lets sat tables are Q1,Q2,Q3,Q4.
I need to merge/join these tables into a single table Q5 such that columns of all tables are present in Q5 but common column should appear once and the value should be null for the columns where data is not available.

Tables are :
237635-q1.png
237636-q2.png
237675-q3.png
237607-q4.png

Required format is :
237622-q5.png

I tried using inner/ outer joins but the situation is that whenever there are NO Addresse who dont have data in more than one table then joins are not usefull as we can join using Addresse ID.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,655 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2022-09-04T22:10:14.297+00:00

    I will have to assume that addressID= 11 is the same across all tables - else the question does not really make sense. This ends up a four-way full join - not for the faint of heart.

       ; WITH FJ1 AS (  
           SELECT AdressID = isnull(T1.AdressID, T2.AdressID), T1.Phone1, T1. Phone2, T2.Email  
          FROM   T1  
          FULL JOIN T2 ON T1.AdressID = T2.AdressID  
       ), FJ2 AS (  
           SELECT AdressID = isnull(T3.AdressID, T4.AdressID), T3.Fax, T4.Mobile  
          FROM   T3  
          FULL JOIN T4 ON T3.AdressID = T4.AdressID  
       )  
       SELECT AdressID = isnull(FJ1.AdressID, FJ2.AdressID), FJ1.Phone, FJ1.Phone2, FJ1.Email, FJ2Fax, FJ2.Mobile  
       FROM   FJ1  
       FULL JOIN FJ2 ON FJ1.AdressID = FJ2.AdressID  
    

2 additional answers

Sort by: Most helpful
  1. Marc Riebel 1 Reputation point
    2022-09-04T21:42:12.277+00:00

    The address is most likely referenced to a person.
    Use this table as a basis for your query.
    Join the address data to it and insert only the necessary columns into your new target table.

    0 comments No comments

  2. CosmogHong-MSFT 22,621 Reputation points Microsoft Vendor
    2022-09-05T02:32:14.537+00:00

    Hi @Samuel
    Please check this query, based on Erland's answer

    SELECT ISNULL(ISNULL(ISNULL(Q1.AddressID,Q2.AddressID),Q3.AddressID),Q4.AddressID) AS AddressID  
          ,Phone1,Phone2,Email,Fax,Mobile  
    FROM Q1 FULL JOIN Q2 ON Q1.AddressID = Q2.AddressID  
            FULL JOIN Q3 ON Q1.AddressID = Q3.AddressID  
    	    FULL JOIN Q4 ON Q1.AddressID = Q4.AddressID  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.