select p.pid 
,CONCAT(c.titlename,p.fname,' ',p.lname) AS 'ชื่อ-สกุล'
,getAgeYearNum (birth,CURDATE()) AS 'อายุ'
,case when (pc.chroniccode is not null and pcdm.chroniccode is null )then 1 else 0 end as dm,min(pc.datefirstdiag) as firstdiaght
,case when (pcdm.chroniccode is not null and pc.chroniccode is null ) then 1 else 0 end as ht,min(pcdm.datefirstdiag) as firstdxdm
,case when pcdm.chroniccode is not null and pc.chroniccode is not null then 1 else 0 end as htdm
 from person p
INNER JOIN ctitle AS c ON p.prename = c.titlecode
left join personchronic pc on p.pid = pc.pid and p.pcucodeperson = pc.pcucodeperson
and trim(pc.chroniccode) = 'I10'
left join personchronic pcdm on p.pid = pcdm.pid and p.pcucodeperson = pcdm.pcucodeperson
and pcdm.chroniccode between 'E10' and 'E15' 
AND p.pid NOT IN ((SELECT persondeath.pid FROM persondeath))
GROUP BY p.pid,ht,dm
having ht = 1 or dm = 1 or htdm =1
order by pid;