Requête: Contacte des membres des clubs de Bombardopolis

select a.id_patient, concat(p.city_code,"/",p.hospital_code) as site, p.patient_number as stcode,
IF(c.club_type = 1,tm.last_name,ti.last_name) as Nom ,
 IF(c.club_type = 1,tm.first_name,ti.first_name) as Prenom,
 IF(c.club_type = 1,"Female",IF(ti.gender=1,"Male",IF(ti.gender=2,"Female","Unknown"))) as sexe,
 IF(c.club_type = 1,tm.dob,ti.dob) as 'Date de naissance',
 c.name as 'Nom du club',
 ct.name as 'Type de club',
 IF(c.club_type=1,tm.telephone, IF(ooc.carer_telephone is null or ooc.carer_telephone ="",ti.carer_telephone, ooc.carer_telephone ) ) as 'no. tél',
 IF(c.club_type=1,tm.address, IF(ooc.carer_address is null or ooc.carer_address ="",ti.carer_address, ooc.carer_address ) ) as 'Adresse',
 IF(c.club_type = 1,oop.mother_gps,ooc.gps) as gps,
 IF(c.club_type = 1,oop.location,ooc.location) as location
from caris_db.club_patient a
left join caris_db.patient p on a.id_patient=p.id
left join caris_db.club c on c.id= a.id_club
left join caris_db.lookup_club_type ct on ct.id= c.club_type
left join caris_db.tracking_infant ti on p.id=ti.id_patient
left join caris_db.tracking_motherbasicinfo tm on tm.id_patient=p.id
left join openfn.odk_child_visit ooc ON ( ooc.patient_code = p.patient_code OR SUBSTRING_INDEX(ooc.caseId,'_',-1) = p.id)
left join openfn.odk_pregnancy_visit oop ON  ( oop.health_id = p.patient_code OR SUBSTRING_INDEX(oop.caseId,'_',-1) = p.id)
where c.id_hospital in (73,74,75,150,155) and concat(p.city_code,"/",p.hospital_code) is not null
group by a.id_patient order by c.club_type, concat(p.city_code,"/",p.hospital_code)