SELECT person.pid AS HN,person.idcard,concat(ctitle.titlename,' ',person.fname,' ',person.lname) AS `name`,
YEAR(FROM_DAYS(DATEDIFF('2015-10-01' ,person.birth ))) AS Age,house.hno AS Hno,village.villno AS moo,
person.typelive AS Type_Area,person.rightcode,person.rightno,person.hosmain,person.hossub,person.dateexpire
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
INNER JOIN cright ON person.rightcode = cright.rightcode
WHERE right(village.villcode,2)!= '00' and person.typelive in (1,3)
and cright.rightgroup in (3,4)and person.hossub != person.pcucodeperson
AND concat(person.pid,person.pcucodeperson)NOT IN (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate <= CURDATE() or deaddate is null)
GROUP BY person.pid;