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
how can you tell the difference between a town and a municipality?
A municipality has the feature_code ADM3
A town has the feature_code PPL
could you give an example of where the municipality is the same as the town?
i'd like to see the entire rows
Sorry for the slow reply.
If the records have the same asciiname I just want the municipality.
They are separate rows in the table
1 calasparra ADM3 (the municipality record)
2 calasparra PPL (the town record)
FROM geo_import AS muni
WHERE feature_code = 'ADM3'
AND province_id = '2509951'
FROM geo_import AS city
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
BY asciiname asc