SELECT person.pid AS HN
,CONCAT(ctitle.titlename,person.fname,' ',person.lname) AS 'ชื่อ'
,person.idcard AS 'CID'
,CONCAT(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) AS 'วันเกิด'
,getagemonth(person.birth,'2013-12-31') AS 'อายุ(เดือน)'
,house.hno AS 'บ้านเลขที่'
,village.villno AS 'หมู่'
,village.villname AS 'บ้าน'
FROM person
INNER JOIN house ON person.hcode=house.hcode AND person.pcucodeperson=house.pcucode
INNER JOIN village ON house.villcode=village.villcode AND house.pcucode = village.pcucode
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN persondeath ON person.pid = persondeath.pid AND person.pcucodeperson = persondeath.pcucodeperson
WHERE persondeath.pid IS NULL
AND person.typelive IN ('0','1','3')
AND right(house.villcode,2)!= '00'
AND getagemonth(person.birth,'2013-12-31') <= 36
AND CONCAT(person.pid,person.pcucodeperson) NOT IN ( SELECT CONCAT(person.pid,person.pcucodeperson) FROM visit
INNER JOIN visitdentalcheck ON visit.visitno = visitdentalcheck.visitno AND visit.pcucode = visitdentalcheck.pcucode
INNER JOIN person ON visit.pid = person.pid AND visit.pcucode = person.pcucodeperson
WHERE visit.visitdate BETWEEN '2013-11-01' AND '2013-12-31')
GROUP BY person.pid
ORDER BY village.villno;