SELECT person.pid,
person.idcard AS '13 หลัก',
ctitle.titlename AS 'คำนำหน้า',
person.fname AS 'ชื่อ',
person.lname AS 'นามสกุล',
person.sex AS 'เพศ',
person.birth AS 'วันเกิด',
getAgeYearNum(person.birth,CURRENT_DATE) AS 'อายุเต็ม',
timestampdiff(year,person.birth,curdate()) as 'อายุปี',
timestampdiff(month,person.birth,curdate())-(timestampdiff(year,person.birth,curdate())*12) as 'อายุเดือน',
timestampdiff(day,date_add(person.birth,interval (timestampdiff(month,person.birth,curdate())) month),curdate()) as 'อายุวัน',
house.hno AS 'บ้านเลขที่',
house.villcode AS 'หมู่'
FROM
person
INNER JOIN house ON person.pcucodeperson = house.pcucode
AND person.hcode = house.hcode
INNER JOIN ctitle ON ctitle.titlecode= person.prename
INNER JOIN village ON house.pcucode = village.pcucode
AND village.villcode = house.villcode
WHERE (person.dischargetype is null or person.dischargetype = 9) and SUBSTRING(house.villcode,7,2) !='00'
AND person.typelive in ('1','3')
GROUP BY house.hno;