declare @country as varchar(2) = 'FR'
 
 select	coalesce(adm1.admin1_code,'') as Admin1Cod,
 		coalesce(adm1.asciiname,'')  as Admin1Descr,
 		coalesce(adm2.admin2_code,'') as Admin2Cod,
 		coalesce(adm2.asciiname,'')  as Admin2Descr,
 		coalesce(adm3.admin3_code,'') as Admin3Cod,
 		coalesce(adm3.asciiname,'')  as Admin3Descr,
 		coalesce(adm4.admin4_code,'') as Admin4Cod,
 		coalesce(adm4.asciiname,'')  as Admin4Descr,
 		p.*
 from	geoname_allCountries as p
 		left outer join	geoname_allCountries as adm1 on adm1.feature_code = 'ADM1' and p.country_code = adm1.country_code and p.admin1_code = adm1.admin1_code
 		left outer join	geoname_allCountries as adm2 on adm2.feature_code = 'ADM2' and p.country_code = adm2.country_code and p.admin1_code = adm2.admin1_code and p.admin2_code = adm2.admin2_code
 		left outer join	geoname_allCountries as adm3 on adm3.feature_code = 'ADM3' and p.country_code = adm3.country_code and p.admin1_code = adm3.admin1_code and p.admin2_code = adm3.admin2_code and p.admin3_code = adm3.admin3_code
 		left outer join	geoname_allCountries as adm4 on adm4.feature_code = 'ADM4' and p.country_code = adm4.country_code and p.admin1_code = adm4.admin1_code and p.admin2_code = adm4.admin2_code and p.admin3_code = adm4.admin3_code and p.admin4_code = adm4.admin4_code
 where	p.feature_class in ('P') 
 		and p.admin1_code not in ('00','')	-- geonames with with wrong administrative divisions
 		and p.country_code = @country
 order by	1,3,5,7,9
 

 asciiname		latitude	longitude	feature_class	feature_code	country_code	admin1_code	admin2_code	admin3_code	admin4_code	population
 Marseille		43.296950	5.381070	P		PPLA		FR		93		13		133		13055		794811
 Beaumont		43.310910	5.434200	P		PPL		FR		93		13		133		13055		0
 Arenc			43.300000	5.366670	P		PPLX		FR		93		13		133		13055		0
 Marseille 01		43.298140	5.384070	P		PPL		FR		93		13		133		13055		40919
 Marseille 02		43.296640	5.370340	P		PPL		FR		93		13		133		13055		25779