Untitled

select 
  c.id as county_id, 
  c.code as county_code, 
  c.name as county_name, 
  s.id as state_id, 
  s.code as state_code, 
  s.name as state_name, 
  cz.id as cresta_id, 
  cz.code as cresta_code, 
  cz.name as cresta_name, 
  ctry.id as country_id, 
  ctry.code as country_code, 
  ctry.name as country_name 
from 
  county c 
  left outer join state s on s.id = c.state_id 
  left outer join cresta_zone cz on cz.id = c.cresta_zone_id 
  join country ctry on ctry.id = c.country_id 
where 
  c.name in (
    select 
      dups.county 
    from 
      (
        select 
          count(name) as count, 
          name as county 
        from 
          county 
        where 
          cresta_zone_id is not null 
        group by 
          name
      ) as dups 
    where 
      dups.count > 1
  ) 
order by 
  --ctry.name,
  --s.name,
  --cz.name, 
  c.name