SELECT
h.villcode AS 'หมู่ที่',
v.villname AS 'หมู่บ้าน',
p.hnomoi AS 'เลขที่',
concat(c.titlename,p.fname,' ',p.lname) AS 'ชื่อสกุล',
getAgeYearNum (p.birth, CURDATE()) AS 'อายุ',
CASE WHEN p.sex = '1' THEN 'ชาย'
WHEN p.sex = '2' THEN 'หญิง'
ELSE p.sex END AS 'เพศ',
p.idcard AS 'เลข ปชช.',
p.privatedoc AS 'ผู้รับผิดชอบ'
FROM
person p
INNER JOIN house h ON p.hcode = h.hcode
AND p.pcucodeperson = h.pcucode
LEFT JOIN village v ON v.villcode = h.villcode
LEFT JOIN personchronic pc ON p.pid = pc.pid
LEFT JOIN ctitle c ON p.prename = c.titlecode
WHERE
RIGHT (v.villcode, 2) <> '00'
AND getAgeYearNum (birth, CURDATE()) >= '15'
AND pc.pid IS NULL
AND nation = '99'
AND typelive IN(1,3)
ORDER BY h.villcode;