arv2

select concat(first_name,' ', last_name) as prenom_nom, name as hospital, patient_code, dob, date_blood_taken, pcr_result_date, if(is_abandoned=1,'abandon','') abandon, carer_name, carer_telephone, carer_address
from(
select id_patient from testing_specimen where pcr_result=1
union
select id_patient from testing_result where result=1
union
select distinct id_patient from tracking_followup
union
select distinct id_patient from tracking_regime where category='regime_infant_treatment'
union
select id_patient from tracking_infant where positive_pcr_1!='0000-00-00' or positive_pcr_2!='0000-00-00'
)x
left join patient on patient.id=x.id_patient
left join tracking_infant on tracking_infant.id_patient=patient.id
left join (
select id_patient, date_blood_taken, pcr_result_date from testing_specimen where pcr_result=1
union
select id_patient, blood_draw_date, result_date as pcr_result_date from testing_result where result=1
)y on y.id_patient = patient.id
left join tracking_regime tr on tr.id_patient=x.id_patient
left join lookup_hospital on concat(lookup_hospital.city_code,'/',lookup_hospital.hospital_code)=left(patient_code,8)
where linked_to_id_patient=0 and is_dead!=0 and left(patient_code,8)
    in ('MAR/CALM', 'STM/CSPP', 'SMA/CSSM', 'GON/CDIR', 'MCH/HCHM', 'HIN/CSMD', 'ARC/SADA', 'PAP/FONP', 'PAP/FOND', 'MON/CSPM', 'PAP/CNSR', 'PAP/CSAB', 'PAP/CSLB', 'PAP/CNSR', 'PAP/GCHO', 'PIG/CSSR', 'CAP/CMCD', 'CAP/CDSF', 'PIG/HOBP', 'CAP/CMSO', 'CAP/CSBL', 'CAP/CMSC', 'CAP/CSLF', 'CAP/CSLJ', 'CAP/CSLS', 'CAP/CALC', 'PDP/CSAF', 'LAT/HNDP', 'BOM/CSBH', 'PDP/CMEB', 'JER/CSSH', 'JER/CSSJ', 'JER/HHFJ', 'DMA/HCDM', 'MIR/CSPT', 'MIR/CDSA', 'MIR/CSJF', 'CAY/CSAL', 'CAY/CDSL', 'CAY/CSIV', 'CAY/SHLI', 'JAC/CALB')
and tr.id_patient is null
group by patient_code