select
person.pid as pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ-นามสกุล',
CONCAT(DATE_FORMAT(person.birth,'%d-%m-'),DATE_FORMAT(person.birth,'%Y')+543) AS 'วันเดือนปีเกิด',
GetAgeYearNum(person.birth,'2015-10-01')as age,
person.idcard as idcard,
person.hnomoi as hno,
village.villno as villno,
case when (pcht.chroniccode is not null and pcdm.chroniccode is null )then 'ป่วย' else '' end as 'ป่วยเป็นความดัน',
case when (pcdm.chroniccode is not null and pcht.chroniccode is null )then 'ป่วย' else '' end as 'ป่วยเป็นเบาหวาน'
from person inner join ctitle on person.prename = ctitle.titlecode
left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson
inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
inner join village on house.pcucode = village.pcucode and village.villcode = house.villcode
where person.typelive in ('1','3') and person.pid not in (select persondeath.pid from persondeath)
and GetAgeYearNum(person.birth,'2015-10-01')>= '35' and substring(house.villcode,7,2)<>'00'
and person.pid not in (select person.pid from person left join personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
left join personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
left join personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson where pcdm.chroniccode is not null and pcht.chroniccode is not null)
group by person.pid
order by villno, person.pid;