SELECT
p.pid AS Hn,
CONCAT(c.titlename,p.fname,' ',p.lname) AS `ชื่อ - สกุล`,
CASE WHEN p.sex = 1 THEN 'ชาย'
WHEN p.sex = 2 THEN 'หญิง' ELSE NULL END เพศ,
p.birth AS `วันเกิด`,
p.idcard AS `เลขบัตรประชาชน`,
h.hno AS `บ้านเลขที่`,
v.villno AS `หมู่ที่`,
max(CASE WHEN p1.chroniccode = 'i10' THEN '/' ELSE NULL END) 'HT',
max(CASE WHEN p1.chroniccode BETWEEN 'e10' AND 'e14.9' THEN '/' ELSE NULL END) 'DM',
GROUP_CONCAT(p1.chroniccode) ชื่อโรค,
CASE WHEN h.area = 1 THEN '/' ELSE NULL END เทศบาล,
CASE WHEN h.area = 2 THEN '/' ELSE NULL END อบต
FROM
person AS p
INNER JOIN house AS h ON p.pcucodeperson = h.pcucode AND p.hcode = h.hcode
INNER JOIN village AS v ON h.pcucode = v.pcucode AND h.villcode = v.villcode
INNER JOIN personchronic AS p1 ON p.pcucodeperson = p1.pcucodeperson AND p.pid = p1.pid
INNER JOIN ctitle AS c ON p.prename = c.titlecode
WHERE
p.pid NOT IN (SELECT persondeath.pid FROM persondeath)
AND p.typelive IN (0,1,3)
GROUP BY
p.pid;