SELECT person.pid
,CONCAT_WS('-',substring(idcard,1,1),substring(idcard,2,4),substring(idcard,6,5),substring(idcard,11,2),substring(idcard,13,1))AS 'เลขบัตรประชาชน'
,CONCAT(ctitle.titlename,' ',person.fname,' ',person.lname) AS 'ชื่อ-นามสกุล'
,CONCAT(person.hnomoi,'หมู่ ',person.mumoi,' ') AS 'ที่อยู'
,CONCAT(DATE_FORMAT(person.birth,'%d-%m-'),DATE_FORMAT(person.birth,'%Y')+543) AS 'วันเดือนปีเกิด'
,YEAR( FROM_DAYS( DATEDIFF( NOW( ) ,person.birth ) ) ) AS 'อายุ'
,persondeath.deadcause AS 'โรคที่ตาย'
,persondeath.deaddate AS 'วันตาย'
,personchronic.chroniccode AS 'โรคเรื้อรัง'
FROM person
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
LEFT JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
LEFT JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
LEFT JOIN personchronic ON person.pid=personchronic.pid
WHERE personchronic.pid IS not NULL
AND persondeath.deaddate BETWEEN '2014-10-01' AND '2015-09-30'
AND chroniccode LIKE 'E%'
GROUP BY person.pid
ORDER BY persondeath.deaddate DESC;
#create by rambo