SELECT o.hn,o.vstdate,o.vsttime,concat(p.pname,p.fname," ",p.lname) AS ptname ,o.main_dep,k.department,o.pttype,t.name AS pttype_name
FROM ovst o
LEFT OUTER JOIN patient p ON p.hn = o.hn
LEFT OUTER JOIN kskdepartment k ON k.depcode=o.main_dep
LEFT OUTER JOIN pttype t ON t.pttype = o.pttype
WHERE o.vstdate BETWEEN "2006-01-01" AND "2006-10-30" AND o.pttype IN (SELECT pttype FROM pttype WHERE pcode="A7")
ORDER BY o.vstdate,o.vsttime