CODEWITHSUNDEEP
mysqlsqlselectunion
OldSteve
OldSteve

Reputation: 608

Select Only Those Items That Appear Only In Each Table Of Two MySQL Databases

I have two databases which started as the same some time ago. These now need merging into one. To avoid duplication I want to delete entries that are in both from the second. If I run the query below it automatically removes duplicates:-

SELECT idClient, LastName, FirstName
   FROM `ABC`.`Client`
   WHERE idClient > 1000
UNION
SELECT  idClient, LastName, FirstName
   FROM `XYZ`.`Client`
   WHERE idClient > 1000;

However, the only entries I want returned are the ones which are duplicated but cannot work out the syntax to achieve this.

Upvotes: 0

Views: 48

Answers (2)

Radim Bača
Radim Bača

Reputation: 10711

Use INNER JOIN

SELECT idClient, LastName, FirstName
FROM `ABC`.`Client` a
JOIN `XYZ`.`Client` b ON a.idClient = b.idClient AND a.LastName = b.LastName AND a.FirstName = b.FirstName
WHERE a.idClient > 1000

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you want the ones that are duplicated, you can use exists:

SELECT idClient, LastName, FirstName
FROM `ABC`.`Client` ac
WHERE idClient > 1000 AND
      EXISTS (SELECT 1
              FROM `XYZ`.`Client` xc
              WHERE xc.idClient = ac.idClient AND xc.LastName = ac.LastName AND
                    xc.FirstName = ac.FirstName AND
                    xc.idClient > 1000
             );

Note: This will not return columns that have NULL values.

Upvotes: 1

Related Questions