CODEWITHSUNDEEP
sqlms-access
zjluoxiao
zjluoxiao

Reputation: 41

How to merge two tables in Access using SQL. Two tables have several common columns but also have several columns not in common

I am trying to merge two tables using query in MSAccess using SQL.

Table 1 has following columns: 

ID, address, rent, tax, basement, garage

Table 2 has following colums:

ID, address, rent, price, sold_date

In the final output, I want

ID, address, rent, tax, price, basement, garage, sold_date together.

The IDs in two table are not same at all. It's kind of like concatenating two tables together with same columns' name link together and leave different columns in the different columes.

I tried using full outer join in MSAccess which is already tricky but require two tables has same structures.

select * from Table1 left join Table2 On Table1.ID = Table2.ID Union select * from Table1 right join Table2 On Table1.ID = Table2.ID

Above codes is my current code which retrive the data without merge the two tables together with the same columns name. This is learned from https://support.office.com/en-us/article/join-tables-and-queries-3f5838bd-24a0-4832-9bc1-07061a1478f6

The output is like:

Table1.ID, Table1.address, Table1.rent, Table1.price, Table1.sold_date, Table2.ID, Table2.address, Table2.rent, Table2.price, Table2.sold_date

Is there a neat way to do this?

Thanks!

Upvotes: 1

Views: 233

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Another method is to generate all the ids and use LEFT JOIN:

select t1.*, t2.*
from ((select id from Table1
       union   -- on purpose to remove duplicates
       select id from table2
      ) as i left join
      Table1 as t1
      on i.ID = t1.ID 
     ) left join
     Table2 as t2
     on i.ID = t2.ID ;

However, Tim's solution should also work. In some versions of MS Access, you may need to replace the UNION with view.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

You may simulate a full outer join in Access using a union of a left and a right join:

SELECT t1.ID, t1.address, t1.rent, t1.tax, t2.price, t2.basement, t2.garage, t2.sold_date 
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
UNION ALL
SELECT t1.ID, t1.address, t1.rent, t1.tax, t2.price, t2.basement, t2.garage, t2.sold_date
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.id IS NULL;

Upvotes: 1

Related Questions