How to merge 4 tables into one.

Question

How to merge 4 tables into one.

Samuel 21

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.

0 comments

Answer accepted by question author

Erland Sommarskog 134.3K MVP Volunteer Moderator

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

Samuel 21 Reputation points

2022-09-05T02:47:07.68+00:00

Hi, thanks for your answer but there can be situation when there is no common Id in all the tables. That's why the joins are not working.
LiHongMSFT-4306 31,621 Reputation points

2022-09-05T03:16:30.907+00:00

there can be situation when there is no common Id in all the tables.That's why the joins are not working.

Then that's why we use FULL JOIN, it will return rows even though id not matched or null.
Or maybe i misunderstood your request.
Samuel 21 Reputation points

2022-09-05T05:29:05.983+00:00

Thanks for the elaboration.
Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2022-09-05T20:41:14.303+00:00

I'm uncertain about your comment. You say that the joins are not working, but you have still accepted my Answer.

If you need more help, please provide CREATE TABLE statements for your tables and the sample data as INSERT statements. Since I cannot copy and paste from an image, it was not possible for me to test my query.

2 additional answers

Your answer

Samuel 21 Reputation points

2022-09-05T02:47:07.68+00:00

Hi, thanks for your answer but there can be situation when there is no common Id in all the tables. That's why the joins are not working.
LiHongMSFT-4306 31,621 Reputation points

2022-09-05T03:16:30.907+00:00

there can be situation when there is no common Id in all the tables.That's why the joins are not working.

Then that's why we use FULL JOIN, it will return rows even though id not matched or null.
Or maybe i misunderstood your request.
Samuel 21 Reputation points

2022-09-05T05:29:05.983+00:00

Thanks for the elaboration.
Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2022-09-05T20:41:14.303+00:00

I'm uncertain about your comment. You say that the joins are not working, but you have still accepted my Answer.

If you need more help, please provide CREATE TABLE statements for your tables and the sample data as INSERT statements. Since I cannot copy and paste from an image, it was not possible for me to test my query.

Answer 1

LiHongMSFT-4306 31,621

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.

Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator

2022-09-05T20:43:36.027+00:00

Li, I don't think that query works, if there are only rows in Q3 and Q4. As I said, a four-way full join is not a trivial thing.

You may be interested in this article that I wrote for the SQLServerGeeks Magazine: https://sqlservergeeks.com/a-tip-about-full-outer-by-erland-sommarskog/.

Answer 2

Marc Riebel 1

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

Share via

How to merge 4 tables into one.

2 additional answers

Your answer