select 
	m.membre_email as email, 
	m.membre_prenom as prenom, 
	m.membre_nom as nom, 
	mc.membre_civilite_libelle as civilite, 
	m.membre_date_naissance as date_naissance, 
	m.membre_adresse as adresse, 
	m.membre_code_postal as code_postal, 
	m.membre_ville as ville, 
	p.pays as pays, 
	membre_date_inscription as date_inscription,
	membre_ip as ip
from membre m
left outer join membre_civilite mc on mc.membre_civilite_id = m.membre_membre_civilite
left outer join pays p on p.id_pays = m.membre_pays
where membre_optin_partenaire = 1

union all

select n.nuage_email, n.nuage_prenom as prenom, n.nuage_nom as nom, n.nuage_civilite, n.nuage_date_naissance, n.nuage_adresse, n.nuage_code_postal, n.nuage_ville, p.pays, nuage_date_inscription as date_inscription, nuage_IP
from nuage n
left outer join pays p on p.id_pays = n.nuage_pays
left outer join membre me on me.membre_email = n.nuage_email
where me.membre_email is null
and nuage_optin = 1

union all

select m.email, m.prenom, m.nom, m.civilite, m.date_naissance, m.adresse, m.code_postal, m.ville, p.pays, date_inscription, IP
from membre_v1 m
left outer join pays p on p.id_pays = m.pays
left outer join nuage n on n.nuage_email = m.email
left outer join membre me on me.membre_email = m.email
where n.nuage_email is null
and me.membre_email is null
and optin = 1

group by email