Re: Conditional left join - Mailing list pgsql-general

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.