SELECT
person.pid AS HN,
person.fname AS `ชื่อ`,
person.lname AS `นามสกุล`,
person.birth AS `วันเกิด`,
person.idcard AS `เลขบัตร ปชช`,
person.hnomoi AS `บ้านเลขที่`,
person.mumoi AS `หมู่ที่`,
person.dateexpire AS 'วันหมดอายุ' ,
p1.osm as 'อสม.' FROM
person
INNER JOIN house ON person.hcode=house.hcode and person.pcucodeperson=house.pcucode
left join (select p1.pid,concat(p1.fname,' ',p1.lname)as osm from person p1)p1 on house.pidvola=p1.pid
INNER JOIN cright ON person.rightcode = cright.rightcode
INNER JOIN crightgroup ON cright.rightgroup = crightgroup.rightgroupcode
WHERE
person.typelive IN (1,3)
AND person.dateexpire < CURRENT_DATE
AND person.pid NOT IN (SELECT persondeath.pid FROM persondeath)
AND crightgroup.rightgroupcode IN (3,4);