How to combine two tables with different column numbers while removing duplicates?
I have two tables in an Access database. Table 1 has more columns than Table 2. I would like to combine these tables into one, removing duplicates. I have the following request
SELECT FirstName, LastName, PhoneNumber FROM Table1
UNION
SELECT FirstName, LastName, Null as PhoneNumber FROM Table2
The problem is, I don't want to copy any record from Table 2 that has the same FirstName and LastName in Table1. How can I change the above query to do this? Thanks in advance.
source to share
Start with a query that only returns rows Table2
that don't match in Table1
.
SELECT t2.FirstName, t2.LastName
FROM
Table2 AS t2
LEFT JOIN Table1 AS t1
ON
t2.FirstName = t1.FirstName
AND t2.LastName = t1.LastName
WHERE t1.FirstName Is Null;
Then use SELECT
in your request UNION
.
SELECT FirstName, LastName, PhoneNumber FROM Table1
UNION ALL
SELECT t2.FirstName, t2.LastName, t2.Null AS PhoneNumber
FROM
Table2 AS t2
LEFT JOIN Table1 AS t1
ON
t2.FirstName = t1.FirstName
AND t2.LastName = t1.LastName
WHERE t1.FirstName Is Null;
Note. I used UNION ALL
because it requires less work from the db engine, so it is faster. Only use UNION
when you want the db engine to cut duplicate lines. But in this case it would not be necessary ... unless the duplicates exist separately in one or both of these tables.
source to share
FaddishWorm has a good concept, but two separate subqueries will eliminate any entry with the same name or the same name. NOT x AND NOT y = NOT (x OR y). Therefore, names like Hernandez and Jim will be omitted from Table 2.
Try concatenating.
SELECT FirstName, LastName, PhoneNumber FROM Table1
UNION
SELECT FirstName, LastName, Null as PhoneNumber FROM Table2
WHERE FirstName & Lastname NOT IN (SELECT FirstName & lastname FROM Table1);
There are other solutions as well. It's slow. HandsUp has the right idea.
source to share