How to merge 4 tables into one.

Samuel 21 Reputation points

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 :

Required format is :

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
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

    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

    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

    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  
    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,

    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.