Pasted

SELECT 
    xcx.*,
    is_abandoned,
    abandoned_date,
    is_dead,
    tracking_infant.death_date,
    IF(tracking_regime.id_patient IS NOT NULL,
        'yes',
        IF(opq.id_patient IS NOT NULL,
            'yes',
            'non')) AS on_arv
FROM
    (SELECT 
        *
    FROM
        (SELECT 
        x.id_patient, date_blood_taken, patient_code, name AS result
    FROM
        (SELECT 
        id_patient,
            date_blood_taken,
            pcr_result AS result,
            created_at
    FROM
        testing_specimen t1
    WHERE
        date_blood_taken = (SELECT 
                MIN(date_blood_taken)
            FROM
                testing_specimen t2
            WHERE
                t1.id_patient = t2.id_patient) UNION SELECT 
        id_patient,
            blood_draw_date AS date_blood_taken,
            result,
            created_at
    FROM
        testing_result t1
    WHERE
        #blood_draw_date != '0000-00-00'
            #AND 
            blood_draw_date = (SELECT 
                MIN(blood_draw_date)
            FROM
                testing_result t2
            WHERE
                t1.id_patient = t2.id_patient)) x
    LEFT JOIN patient ON patient.id = x.id_patient
    LEFT JOIN lookup_testing_lab_result ON lookup_testing_lab_result.id = x.result
    WHERE
        linked_to_id_patient = 0) op1
    WHERE
        date_blood_taken = (SELECT 
                MIN(date_blood_taken)
            FROM
                (SELECT 
                x.id_patient, date_blood_taken, patient_code, name AS result
            FROM
                (SELECT 
                id_patient,
                    date_blood_taken,
                    pcr_result AS result,
                    created_at
            FROM
                testing_specimen t1
            WHERE
                date_blood_taken = (SELECT 
                        MIN(date_blood_taken)
                    FROM
                        testing_specimen t2
                    WHERE
                        t1.id_patient = t2.id_patient) UNION SELECT 
                id_patient,
                    blood_draw_date AS date_blood_taken,
                    result,
                    created_at
            FROM
                testing_result t1
            WHERE
                #blood_draw_date != '0000-00-00'
                   # AND 
                    blood_draw_date = (SELECT 
                        MIN(blood_draw_date)
                    FROM
                        testing_result t2
                    WHERE
                        t1.id_patient = t2.id_patient)) x
            LEFT JOIN patient ON patient.id = x.id_patient
            LEFT JOIN lookup_testing_lab_result ON lookup_testing_lab_result.id = x.result
            WHERE
                linked_to_id_patient = 0) op2
            WHERE
                op1.id_patient = op2.id_patient)
    GROUP BY id_patient) xcx
        LEFT JOIN
    tracking_infant ON tracking_infant.id_patient = xcx.id_patient
        LEFT JOIN
    tracking_regime ON tracking_regime.id_patient = xcx.id_patient
        LEFT JOIN
    (SELECT 
        id_patient
    FROM
        tracking_followup
    WHERE
        on_arv = 1) opq ON opq.id_patient = xcx.id_patient
GROUP BY id_patient