select EPI.pid,EPI.fname,EPI.lname,EPI.birth,EPI.age,EPI.DTP4,EPI.OPV4,EPI.JE1,EPI.JE2
,case when (EPI.DTP4 and EPI.OPV4 and EPI.JE2 )!="0" then 'ครบ'else'ไม่ครบ'end as 'ความครอบคลุม'
,EPI.pcucodeperson
FROM
(SELECT person.pcucodeperson,person.pid,person.birth,person.fname,person.lname,getagemonth(person.birth,CURDATE() )as age
,max(case when cdrug.files18epi='034'then visitepi.dateepi else " 0 " end)as DTP4
,max(case when cdrug.files18epi='035'then visitepi.dateepi else " 0 " end)as DTP5
,max(case when cdrug.files18epi='081'then visitepi.dateepi else " 0 " end)as OPV1
,max(case when cdrug.files18epi='082'then visitepi.dateepi else " 0 " end)as OPV2
,max(case when cdrug.files18epi='083'then visitepi.dateepi else " 0 " end)as OPV3
,max(case when cdrug.files18epi='084'then visitepi.dateepi else " 0 " end)as OPV4
,max(case when cdrug.files18epi='085'then visitepi.dateepi else " 0 " end)as OPV5
,max(case when cdrug.files18epi='061'then visitepi.dateepi else " 0 " end)as MMR
,max(case when cdrug.files18epi='051'then visitepi.dateepi else " 0 " end)as JE1
,max(case when cdrug.files18epi='052'then visitepi.dateepi else " 0 " end)as JE2
,max(case when cdrug.files18epi='053'then visitepi.dateepi else " 0 " end)as JE3
FROM person
INNER JOIN house on person.hcode=house.hcode AND person.pcucodeperson=house.pcucodeperson
LEFT JOIN visitepi on person.pid=visitepi.pid AND person.pcucodeperson=visitepi.pcucodeperson
LEFT JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode
WHERE person.typelive in ('1','3')
and getagemonth(person.birth,CURDATE() ) between 24 and 35
GROUP BY CONCAT(person.pcucodeperson,person.pid))as EPI 
WHERE EPI.DTP4 = 0 or EPI.OPV4= 0 or EPI.JE2= 0
GROUP BY EPI.pid
ORDER BY EPI.birth