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.
9,174 questions
Accepted answer
  1. Erland Sommarskog 72,891 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
Most helpful Newest Oldest
  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.

  2. CosmogHong-MSFT 9,161 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.