SQL advanced -> Find Dubious Translations in Address list

In the following example we will find dubious spellings of City value in the address list. In our address table we store City values in two languages: English and Italian. Time came to check if translation is always the same. Cause if it is not, we will have to make corrections.

The source dataset looks like this:
idCity_ENCity_ITStreet
1MilanMilanoVia XX Settembre
2TurinTorinoCorso Vittorio Emanuele
3RomeRomaVia Falci
4MilanMilanVia Napoleone
5RomeRomeVia Piovesi
6MilanMilanoPiazza Partigiani
............


First we will group all possible combinations of ENG/ITA city names pairs:
SELECT City_EN, City_IT
FROM Address_Table
GROUP BY City_EN, City_IT

And from the first glance can say that there is lack of uniformity. While we wish to have single possible translation of English to Italian:
City_ENCity_IT
MilanMilan
MilanMilano
RomeRoma
RomeRome
TurinTorino

At the next step we will identify English values that was translated to Italian in many ways:

SELECT City_EN, count(*) AS cn
FROM
(
  SELECT City_EN, City_IT
  FROM Address_Table
  GROUP BY City_EN, City_IT
) AS a
GROUP BY City_EN HAVING count(*) > 1

Recieving something like this:
City_ENcn
Milan2
Rome2

Finally we will find all Address records that need to be revised and possibly be fixed:
SELECT c.*
FROM Address_Table AS c INNER JOIN
(
  SELECT City_EN, count(*) AS cn
  FROM
  (
    SELECT City_EN, City_IT
    FROM Address_Table
    GROUP BY City_EN, City_IT
  ) AS a
  GROUP BY City_EN HAVING count(*) > 1
) AS b ON c.City_EN = b.City_EN
ORDER BY c.City_EN, c.City_IT;


And here is our final dataset:
idCity_ENCity_ITStreet
4MilanMilanVia Napoleone
1MilanMilanoVia XX Settembre
6MilanMilanoPiazza Partigiani
3RomeRomaVia Falci
5RomeRomeVia Piovesi



sqlexamples.info