SQL: find duplicates, with a different field

Question

I have to find duplicates in an Access table, where one field is different. I'll try to explain: assuming to have this data set

ID  Country     CountryB        Customer 
====================================================
1   Italy       Austria         James
2   Italy       Austria         James
3   USA         Austria         James

I have to find all the records with duplicated CountryB and Customer, but with different Country.

For instance, with the data above, the ID 1 and 2 are NOT duplicated (as they are from the same Country), while 1 and 3 (or 2 and 3) are. The "best" query I got is the following one:

SELECT COUNT(*),  CountryB, Customer FROM
(SELECT MIN(ID) as MinID, Country, CountryB, Customer FROM myTable GROUP BY Country, CountryB, Customer)
GROUP BY  CountryB, Customer
HAVING COUNT(*)>1

I'm not sure if this is the smartest option, anyhow. Furthermore, since I need to "mark" all the duplicates, I have to do something more, like this:

SELECT ID, a.Country, a.CountryB, a.Customer FROM  myTable a
INNER JOIN
(
SELECT COUNT(*),  CountryB, Customer FROM
(SELECT MIN(ID) as MinID, Country, CountryB, Customer FROM myTable GROUP BY Country, CountryB, Customer)
GROUP BY  CountryB, Customer
HAVING COUNT(*)>1
) dt
ON a.Country=dt.Country and a.CountryB=dt.CountryB and a.Customer=dt.Customer

Any suggestion this approach is greatly appreciated.

@daniel: actually, on the table, I have another field (`IsDuplicated`). I'd like to set this field as TRUE for any "duplicated" field (where duplicated means what written above) — Nova, Sep 29 '15 at 09:09
As I can't understand what meas "duplicated" from above. Maybe in short, is ID **2** "duplicated"? — daniel, Sep 29 '15 at 09:22
BTW, just review your sql, you second group by can be removed and the having clause added to the first group by. — daniel, Sep 29 '15 at 09:51
@daniel: no, in short: ID **3** is "duplicated". I have to find all the records with the same `CountryB`, `Customer`, and **different** `Country`. Records 1 and 2 are not duplicated one another, since they have the same Country. — Nova, Sep 29 '15 at 11:00
so you want group by Country, CountryB, Customer having count(*) = 1? — daniel, Sep 29 '15 at 11:32

score 1 · Accepted Answer · edited May 23 '17 at 11:44

I finally found a solution. The correct solution is in this answer:

SELECT DISTINCT HAVING Count unique conditions

Adapted with this version, since I'm using Access 2010:

Count Distinct in a Group By aggregate function in Access 2007 SQL

Therefore, in my example table above, I can use this query to find duplicate records:

SELECT CountryB, Customer, Count(cd.Country)
FROM (SELECT DISTINCT Country, CountryB, Customer FROM myTable) AS cd 
GROUP BY CountryB, Customer
HAVING COUNT(*) > 1

or this query to find all the IDs of the duplicated records:

SELECT ID FROM myTable a INNER JOIN
(
SELECT CountryB, Customer, Count(cd.Country)
FROM (SELECT DISTINCT Country, CountryB, Customer FROM myTable) AS cd 
GROUP BY CountryB, Customer
HAVING COUNT(*) > 1
) dt
ON a.CountryB=dt.CountryB AND a.Customer=dt.Customer

SQL: find duplicates, with a different field

1 Answers1