set @str = '2013-10-01';
set @sto = '2014-09-30';
SELECT
panc.*,
vanc.visitdate,
vanc.xx
FROM
(SELECT
person.pcucodeperson,
person.pid,
person.idcard,
concat(ctitle.titlename,person.fname,' ',person.lname) as pname,
person.birth,
getagemonth(person.birth,now()) as 'อายุเป็นเดือน',
house.hno,
right(house.villcode,2) as moo,
cdrug.files18epi
FROM
house
inner join person on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
inner join visitepi on person.pcucodeperson = visitepi.pcucodeperson and visitepi.pid = person.pid
inner join cdrug on cdrug.drugcode = visitepi.vaccinecode
left join ctitle on ctitle.titlecode = person.prename
where visitepi.dateepi BETWEEN @str and @sto and visitepi.hosservice = visitepi.pcucode and cdrug.files18epi in ('051','061')
GROUP BY person.pcucodeperson,person.pid) as panc
LEFT JOIN
(SELECT
visit.pcucode,
visit.visitno,
visit.visitdate,
visit.pcucodeperson,
visit.pid,
GROUP_CONCAT(visitdrug.drugcode) as xx
FROM
visit
INNER JOIN visitdrug ON visit.pcucode = visitdrug.pcucode AND visit.visitno = visitdrug.visitno
where visit.visitdate BETWEEN @str and @sto and visitdrug.drugcode in ('2330011','2338610')
GROUP BY visit.pcucodeperson,visit.pid) as vanc
on panc.pcucodeperson = vanc.pcucodeperson and panc.pid = vanc.pid
ORDER BY files18epi,moo;