SELECT pid,diagcode
,dxtype
,doctordiag
,drugcode

FROM visitdiag
LEFT JOIN visit ON visitdiag.visitno = visit.visitno
LEFT JOIN visitdrug ON visitdiag.visitno = visitdrug.visitno

WHERE diagcode BETWEEN 'Z20' AND 'Z27'
AND drugcode IS NULL
AND visitdate BETWEEN '2014-10-01' AND '2015-09-30';