I have a table which contains towns and municipalities in Spain.
I want users to select a distinct geonameid which corresponds to a town or municipality.
If the municipality asciiname is the same as a town name, i just want the municipality.
This query is incorrect as it returns both the town and municipalities.
select distinct geonameid, asciiname from geo_import where (feature_code = ‘ADM3’ OR feature_code = ‘PPL’) AND province_id = ‘2509951’ order by asciiname asc
SELECT geonameid
, asciiname
FROM geo_import AS muni
WHERE feature_code = 'ADM3'
AND province_id = '2509951'
UNION ALL
SELECT geonameid
, asciiname
FROM geo_import AS city
LEFT OUTER
JOIN geo_import AS muni
ON muni.asciiname = city.asciiname
WHERE city.feature_code = 'PPL'
AND city.province_id = '2509951'
AND muni.asciiname IS NULL
ORDER
BY asciiname asc