|
|
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:
| CustomerID | CustomerName | City | Street | housenum |
| 26 | Arian Aspin | keflavik | nordstrum | 4 |
| 25 | Xena Aspin | keflavik | nordstrum | 4 |
| 10 | Erika Nass | keflavik | suho | 2 |
| 15 | John Depp | keflavik | SuHo | 2 |
| 23 | Alain Fishborn | wellington | turlington | 18 |
| 24 | Adel Fishborn | wellington | turlington | 18 |
sqlexamples.info
|