On 1 December 2011 13:16, Amitabh Kant <amitabhkant@gmail.com> wrote:
> I am trying (through conditional left join?) to fetch all records of tbldata
> and the operator name from tbloperators who was operating the unit at event
> time. If no operator was present, it should return null.
I think you want something akin to:
SELECT *
FROM tbldata AS a
LEFT JOIN (tbloperatorschedule INNER JOIN tbloperator USING (operator_id)) AS b
ON (a.unit_id = b.unit_id AND
a.event_time BETWEEN b.schedule_start_time AND b.schedule_end_time
)
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.