SELECT sites.code_site,
	sites.ref_typmilieu_fede,
	ss_req.id_typmilieu_fede
FROM sites
JOIN (
	SELECT sites_emprise.codsit,
		sit_typo_milieu_fede.*
	from spatial.sites_emprise
	JOIN sit_typo_milieu_fede
	ON sites_emprise.tmilieux=sit_typo_milieu_fede.milieu) AS ss_req
ON sites.code_site=ss_req.codsit
