SQL advanced -> Find records with the same address

The mission is to identify customer records with the same address. It can be necessary for preparing mailing list for advertising letters. In order to save money, we would like to send one letter per address, although some customers reported the same address many times when made their purchases.

First we make query grouping address fields. Then we make join between Customers table and this query:
SELECT a.CustomerID, a.CustomerName, a.Country, a.City, a.Street, a.housenum
FROM Customers AS a INNER JOIN
  (SELECT Country, City, Street, housenum, count(*) AS cn
   FROM Customers
   GROUP BY Country, City, Street, housenum
   HAVING count(*) > 1) AS b
ON (a.Country = b.Country) AND (a.City = b.City)
AND (a.Street = b.Street) AND (a.housenum = b.housenum)
ORDER BY a.Country, a.City, a.Street, a.housenum;

The following query produces editable dataset (in MS-Access terms) on the basis of the duplicates search defined in the previous example:
SELECT CustomerID, CustomerName, City, Street, housenum
FROM Customers
WHERE CustomerID IN
(SELECT a.CustomerID
  FROM Customers AS a INNER JOIN
   (SELECT Country, City, Street, housenum, count(*) AS cn
    FROM Customers
    GROUP BY Country, City, Street, housenum
    HAVING count(*) > 1) AS b
  ON (a.Country = b.Country) AND (a.City = b.City)
  AND (a.Street = b.Street) AND (a.housenum = b.housenum))
ORDER BY City, Street, housenum;

The result will look like this:
CustomerIDCustomerNameCityStreethousenum
26Arian Aspinkeflaviknordstrum4
25Xena Aspinkeflaviknordstrum4
10Erika Nasskeflaviksuho2
15John DeppkeflavikSuHo2
23Alain Fishbornwellingtonturlington18
24Adel Fishbornwellingtonturlington18


sqlexamples.info