audit_enfant

select * from(select patient_code, patient.id, if(which_pcr=1 and year(date_blood_taken)=2016 and (pcr_result=1 or pcr_result=2),'yes','no') as eid_depiste,
if(patient.id in (
select id_patient from(
select id_patient from tracking_followup where year(date)=2016
union
select id_patient from tracking_regime where year(start_date)=2016 and category='regime_infant_treatment'
union
select id_patient from tracking_infant where year(positive_pcr_1)=2016 or year(positive_pcr_2)=2016
)df),
'yes','no')as positive
from patient
left join testing_specimen on testing_specimen.id_patient=patient.id)d
where eid_depiste !='no' or positive!='no'