    select
    tmp.pcucodeperson,
    tmp.pid,
    tmp.titlename,
    tmp.fname,
    tmp.lname,
    tmp.birth,
    tmp.hno,
    tmp.villcode,
    tmp.pcucode,
    tmp.visitno,
    tmp.visitdate,
    tmp.diag,
    cd.drugcode,
    cd.drugname
    from
    (SELECT
    person.pcucodeperson,
    person.pid,
    ctitle.titlename,
    person.fname,
    person.lname,
    person.birth,
    house.hno,
    house.villcode,
    visit.pcucode,
    visit.visitno,
    visit.visitdate,
    group_concat(visitdiag.diagcode) as diag
    FROM
    house
    INNER JOIN person ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
    INNER JOIN visit ON person.pcucodeperson = visit.pcucodeperson AND person.pid = visit.pid
    INNER JOIN visitdiag ON visit.pcucode = visitdiag.pcucode AND visit.visitno = visitdiag.visitno
    INNER JOIN visitdrug ON visitdiag.pcucode = visitdrug.pcucode AND visitdiag.visitno = visitdrug.visitno
    INNER JOIN ctitle ON person.prename = ctitle.titlecode
    INNER JOIN cdrug ON visitdrug.drugcode = cdrug.drugcode
    WHERE cdrug.drugtype = '10' and visit.visitdate between '2013-10-01' and '2014-09-30' # ระบุช่วงวันที่ ที่ต้องการ
    group by visit.pcucode,visit.visitno
    having diag not like '%U%') as tmp
    inner join visitdrug vd on tmp.pcucodeperson = vd.pcucode AND tmp.visitno = vd.visitno
    inner join cdrug cd on vd.drugcode = cd.drugcode
    where cd.drugtype = '10'