mother and child with pcr

SELECT patient.patient_code as mother_code, ddr, dpa, positive_child, children_eid_result, office FROM
tracking_motherbasicinfo
left join caris_db.view_eid_snapshot_linked_children_grouped_by_mother on view_eid_snapshot_linked_children_grouped_by_mother.id_patient_mother = tracking_motherbasicinfo.id_patient
left join patient on tracking_motherbasicinfo.id_patient = patient.id
left join tracking_pregnancy on tracking_pregnancy.id_patient_mother = view_eid_snapshot_linked_children_grouped_by_mother.id_patient_mother
left join lookup_hospital on concat(lookup_hospital.city_code,'/',lookup_hospital.hospital_code) = left(patient_code,8)
where view_eid_snapshot_linked_children_grouped_by_mother.id_patient_mother!=0
and dpa>='2017-08-01';