/* selection stricte evenement AND commune AND site */
SELECT fds_evenement.*, communes.nom as nomcom, sites.libl_site FROM fds_evenement,communes, sites
WHERE (fds_evenement.insee = communes.numero
	AND fds_evenement.newcodesite = sites.code_site);

/* jointure evenement + communes */
SELECT fds_evenement.*,communes.nom FROM fds_evenement
LEFT JOIN communes
ON fds_evenement.insee = communes.numero;

CREATE OR REPLACE VIEW vu_fds_event_j_comun_j_site AS 
 SELECT event_com.num_evenement, event_com.date, event_com.duree,
	event_com.salaries, event_com.benevoles, event_com.ref_destination,
	event_com.ref_operation, event_com.ref_nature_anim, event_com.ref_type_scolaire,
	event_com.presse_avs, event_com.radio_avs, event_com.tele_avs, event_com.affiche_avs,
	event_com.calendrier_avs, event_com.presse_aps, event_com.radio_aps, event_com.tele_aps,
	event_com.adherents, event_com.enfants, event_com.non_adherents,
	event_com.presence_journalistes, event_com.presence_tv, event_com.presence_radio,
	event_com.prix_par_pers, event_com.persons_payant, event_com.persons_gratuit,
	event_com.id_depart, event_com.commentaire1, event_com.newcodesite,
	event_com.insee, event_com.nomcomun, sites.libl_site
FROM ( SELECT fds_evenement.num_evenement, fds_evenement.date,
		fds_evenement.duree, fds_evenement.salaries, fds_evenement.benevoles,
		fds_evenement.ref_destination, fds_evenement.ref_operation,
		fds_evenement.ref_nature_anim, fds_evenement.ref_type_scolaire,
		fds_evenement.presse_avs, fds_evenement.radio_avs, fds_evenement.tele_avs,
		fds_evenement.affiche_avs, fds_evenement.calendrier_avs, fds_evenement.presse_aps,
		fds_evenement.radio_aps, fds_evenement.tele_aps, fds_evenement.adherents,
		fds_evenement.enfants, fds_evenement.non_adherents,
		fds_evenement.presence_journalistes, fds_evenement.presence_tv,
		fds_evenement.presence_radio, fds_evenement.prix_par_pers,
		fds_evenement.persons_payant, fds_evenement.persons_gratuit, fds_evenement.id_depart,
		fds_evenement.commentaire1, fds_evenement.newcodesite,
		fds_evenement.insee, communes.nomcomun
           FROM fds_evenement
      LEFT JOIN communes ON fds_evenement.insee = communes.numero::bpchar) event_com
   LEFT JOIN sites ON event_com.newcodesite = sites.code_site;

ALTER TABLE vu_fds_event_j_comun_j_site OWNER TO postgres;
COMMENT ON VIEW vu_fds_event_j_comun_j_site IS 'table des evenements avec les noms des communes et les libelles de sites CPNRC';