select
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth as birth,
GetAgeYearNum(person.birth,curdate()) as 'อายุ',
person.idcard as idcard,
SUBSTRING(house.villcode,7,2) as moo,
person.hnomoi,
case when visitdiag.diagcode = 'Z71.3' then visit.visitdate else null end as 'วันที่ปรับพฤติกรรม',
group_concat(ccounseling.counselingname)
from person inner join ctitle on person.prename = ctitle.titlecode
inner join visit on person.pcucodeperson = visit.pcucode AND person.pid = visit.pid
inner join visitdiag on visit.visitno = visitdiag.visitno
left join visitcounseling on visit.visitno = visitcounseling.visitno
inner join ccounseling on visitcounseling.counselingcode = ccounseling.counselingcode
left join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
left join village on house.villcode = village.villcode and house.pcucode = village.pcucode
where substring(house.villcode,7,2) !='00' # and GetAgeYearNum(person.birth,curdate()) >= '15'
and visitdiag.diagcode = 'Z71.3' and visit.visitdate between '2014-01-01' and '2015-09-30'
group by person.pid;