SELECT person.pid AS HN
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) AS 'name'
,YEAR(FROM_DAYS(DATEDIFF(CURDATE(),person.birth))) as Age,person.idcard as CID,house.hno as Hno,village.villno AS moo
,GROUP_CONCAT(distinct pc.chroniccode ORDER BY pc.chroniccode ) as chroniccode
,pb.ciga,pb.wisky
FROM
person
INNER JOIN house ON person.hcode = house.hcode AND person.pcucodeperson = house.pcucode
INNER JOIN village ON house.villcode = village.villcode AND house.pcucode = village.pcucode
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN personchronic pc ON person.pcucodeperson = pc.pcucodeperson AND person.pid = pc.pid
left JOIN personbehavior pb ON person.pcucodeperson = pb.pcucodeperson AND person.pid = pb.pid
WHERE (pc.chroniccode between 'E10' and 'E14.9' or pc.chroniccode between 'I10' and 'I15.9')
and pc.typedischart ='03'
and right(village.villcode,2)!= '00'
and person.typelive in (0,1,3)
and person.dischargetype=9
and pb.ciga is null
GROUP BY person.pid;