SELECT
p.pid,
ifnull(c.titlename,p.prename) AS คำนำ,
p.fname as ชื่อ,
p.lname as สกุล,
p.birth as 'วันเดือนปี เกิด',
(getAgeYearNum(birth,curdate())) as'ปี',
(getAgeMonthNum(birth,curdate()))AS' เดือน ',
(getAgeDayNum(birth,curdate()))AS' วัน',
h.hno as บ้านเลขที่,
right(h.villcode,2) AS หมู่ที่,
max(if(ve.vaccinecode='BCG',ve.dateepi,0)) as BCG
,max(if(ve.vaccinecode='HBV1',ve.dateepi,0)) as HBV1
,max(if(ve.vaccinecode in('DHB1','DTP1'),ve.dateepi,0)) as DHB1DTP1
,max(if(ve.vaccinecode='OPV1',ve.dateepi,0)) as OPV1
,max(if(ve.vaccinecode in('DHB2','DTP2'),ve.dateepi,0)) as DHB2DTP2
,max(if(ve.vaccinecode='OPV2',ve.dateepi,0)) as OPV2
,max(if(ve.vaccinecode in('DHB3','DTP3'),ve.dateepi,0)) as DHB3DTP3
,max(if(ve.vaccinecode='OPV3',ve.dateepi,0)) as OPV3
,max(if(ve.vaccinecode in ('MEAS','MMR'),ve.dateepi,0)) as หัด
,max(if(ve.vaccinecode='DTP4',ve.dateepi,0)) as DTP4
,max(if(ve.vaccinecode='OPV4',ve.dateepi,0)) as OPV4
,max(if(ve.vaccinecode='JE1',ve.dateepi,0)) as JE1
,max(if(ve.vaccinecode='JE2',ve.dateepi,0)) as JE2
,max(if(ve.vaccinecode='JE3',ve.dateepi,0)) as JE3
,max(if(ve.vaccinecode='J11',ve.dateepi,0)) as J11
,max(if(ve.vaccinecode='J12',ve.dateepi,0)) as J12
,max(if(ve.vaccinecode='mmr2',ve.dateepi,0)) as mmr2
,max(if(ve.vaccinecode='mrc',ve.dateepi,0)) as mrc
,max(if(ve.vaccinecode='DTP5',ve.dateepi,0)) as DTP5
,max(if(ve.vaccinecode='OPV5',ve.dateepi,0)) as OPV5
FROM person AS p
INNER JOIN house AS h ON p.pcucodeperson = h.pcucode AND p.hcode = h.hcode
INNER JOIN ctitle AS c ON p.prename = c.titlecode
LEFT JOIN visitepi ve ON p.pid = ve.pid AND p.pcucodeperson = ve.pcucodeperson
WHERE (getAgeYearNum(birth,curdate())='6' and getAgeMonthNum(birth,curdate())='0') or getAgeYearNum(birth,curdate())<='6' and p.typelive in ('1','3')
GROUP BY p.pid
ORDER BY birth DESC;