SELECT o.vstdate,o.vsttime,o.hn,d.*,dt.name,concat(p.pname,p.fname,' ',p.lname) AS ptname ,t.name AS pttype_name
FROM pq_doctor d
LEFT OUTER JOIN doctor dt ON dt.code = d.doctor
LEFT OUTER JOIN ovst o ON o.vn = d.vn
LEFT OUTER JOIN patient p ON p.hn = o.hn
LEFT OUTER JOIN pttype t ON t.pttype = o.pttype
WHERE d.doctor = '07' AND d.doctor_date BETWEEN '2005-11-01' AND '2006-11-01'
ORDER BY doctor_date,doctor_time