SELECT
visit.visitdate,visit.visitno,
visit.pid,visitdrug.drugcode,
cdrug.drugname
FROM visit
INNER JOIN visitdrug ON visit.pcucode = visitdrug.pcucode AND visit.visitno = visitdrug.visitno
INNER JOIN cdrug ON visitdrug.drugcode = cdrug.drugcode
where(cdrug.drugtype =02 and cdrug.drugtypesub =4 or cdrug.drugtype =10)
and visit.visitdate BETWEEN '2014-10-01' and '2015-09-30'
GROUP BY visit.visitno
ORDER BY visit.visitdate;