Get a distinct value

[tangledman]

Hi

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]

how can you tell the difference between a town and a municipality?

[tangledman]

A municipality has the feature_code ADM3
A town has the feature_code PPL

[r937]

could you give an example of where the municipality is the same as the town?

i'd like to see the entire rows

[tangledman]

Sorry for the slow reply.

If the records have the same asciiname I just want the municipality.

They are separate rows in the table

geonameid
asciiname
feature_code

1 calasparra ADM3 (the municipality record)
2 calasparra PPL (the town record)

[r937]

untested:

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