SELECT person.pid
,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 วันเกิด
,getAgeYearNum(person.birth,CURDATE()) AS อายุ
,house.hno as บ้านเลขที่
,village.villno AS หมู่
,person.rightcode
,rightno
,concat(substr(person.datestart,9,2),'/',substr(person.datestart,6,2),'/',substr(person.datestart,1,4)+543) as datestart
FROM person
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
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
WHERE person.typelive in (0,1,3)
and person.dischargetype='9'
and person.rightcode = '74'
and person.pid not in (SELECT personunable.pid FROM personunable)
ORDER BY village.villno;