Pasted

SELECT 
    patient.patient_code AS infant_code,
    COALESCE(date_of_birth, infant_dob) AS infant_dob,
    CONCAT(testing_mereenfant.mother_city_code,
            '/',
            testing_mereenfant.mother_hospital_code,
            '/',
            testing_mereenfant.mother_code) AS mother_code_on_form,
    x.patient_code AS mother_code_on_hiv,
    date_blood_taken,
    name AS pcr_result
FROM
    testing_specimen
        LEFT JOIN
    testing_mereenfant ON testing_mereenfant.id_patient = testing_specimen.id_patient
        LEFT JOIN
    (SELECT 
        patient_code
    FROM
        tracking_motherbasicinfo
    LEFT JOIN patient ON patient.id = tracking_motherbasicinfo.id_patient) x ON x.patient_code = CONCAT(testing_mereenfant.mother_city_code,
            '/',
            testing_mereenfant.mother_hospital_code,
            '/',
            testing_mereenfant.mother_code)
        LEFT JOIN
    patient ON patient.id = testing_specimen.id_patient
        LEFT JOIN
    lookup_testing_lab_result ON lookup_testing_lab_result.id = pcr_result