SELECT person.pid,
concat(ctitle.titlename,person.fname,' ',person.lname) AS 'ชื่อ-สกุล',
getAgeYearNum(person.birth,CURDATE()) AS 'อายุ',
house.hno AS 'บ้านเลขที่',
village.villno AS 'หมู่ที่',
person.typelive,
cnation.nationname
FROM
person
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
INNER JOIN ctitle ON ctitle.titlecode = person.prename
LEFT JOIN cnation ON person.nation=cnation.nationcode
WHERE nation <>'99'
AND typelive <>'4'
#AND RIGHT(village.villcode,2)<> '00' 
and person.pid not in (select persondeath.pid from persondeath) 
GROUP BY person.pid;