select o.* ,oi.bw,concat(pt.pname,pt.fname,'  ',pt.lname) as ptname,s.status_name,e.emergency_name,
r.room_name,i.name as provision_name ,d.name as request_doctor_name  ,
w.name as ward_name,sp.name as spclty_name,ot.name as operation_type_name,otn.operation_time_type_name
from operation_list o  left outer join patient pt on pt.hn=o.hn
left outer join operation_status s on s.status_id=o.status_id
left outer join operation_emergency e on e.emergency_id=o.emergency_id
left outer join operation_room r on r.room_id=o.room_id
left outer join operation_screen_in oi on oi.operation_id = o.operation_id
left outer join icd101 i on i.code=substring(o.provision_diagnosis,1,3)
left outer join doctor d on d.code=o.request_doctor
left outer join ipt t on t.an = o.an
left outer join ward w on w.ward = t.ward
left outer join spclty sp on sp.spclty = o.spclty
left outer join operation_type ot on ot.operation_type_id = o.operation_type_id
left outer join operation_time_type otn on otn.operation_time_type_id = o.operation_time_type_id
where  o.request_operation_date between '2011-07-01' and '2011-07-25'
order by o.operation_id