tangledman — 2010-03-28T16:04:42-04:00 — #1
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
r937 — 2010-03-29T06:54:10-04:00 — #2
how can you tell the difference between a town and a municipality?
tangledman — 2010-03-29T17:28:00-04:00 — #3
A municipality has the feature_code ADM3
A town has the feature_code PPL
r937 — 2010-03-29T20:31:03-04:00 — #4
could you give an example of where the municipality is the same as the town?
i'd like to see the entire rows
tangledman — 2010-03-31T13:30:34-04:00 — #5
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)
r937 — 2010-03-31T17:00:39-04:00 — #6
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