ARV

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 view_patient_positive.id, patient_code, id_hospital from view_patient_positive
left join tracking_regime on view_patient_positive.id = tracking_regime.id_patient
where tracking_regime.id_patient is null and linked_to_id_patient=0)x
left join tracking_infant on tracking_infant.id_patient = x.id
left join lookup_hospital on lookup_hospital.id=x.id_hospital
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 as date_blood_taken, result_date as pcr_result_date  from testing_result where result=1
    )y on y.id_patient=x.id
    where 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', 'PEG/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')
group by patient_code