SELECT person.pcucodeperson,person.pid,concat(ctitle.titlename,person.fname," ",person.lname)as'ชื่อ-สกุล',DATE_FORMAT(person.birth,'%Y-%m-%d')as birth
,getagemonth(person.birth,CURDATE() )as age_now,getagemonth(person.birth,epi.dateepi)as age_EPI,if(house.hno != '' ,CONVERT(concat(house.hno,' ม.',villno) USING utf8),
CONVERT(concat('- ม.',villno) USING utf8) ) as address
, DATE_FORMAT(epi.dateepi,'%Y-%m-%d')as 'MMR'
,epi.hosservice
FROM person
INNER JOIN house on person.hcode=house.hcode AND person.pcucodeperson=house.pcucodeperson
INNER JOIN village on house.villcode = village.villcode and house.pcucode = village.pcucode
INNER JOIN ctitle on person.prename=ctitle.titlecode
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode WHERE cdrug.files18epi='061')as epi on person.pid=epi.pid
WHERE person.typelive in ('0','1','3') and right(house.villcode,2)!='00'
and person.birth between '20120401' and '20130331'
GROUP BY CONCAT(person.pcucodeperson,person.pid)
ORDER BY person.birth	;