I have the following table structure on Postgres 8.4 : STRUCTURE: tblunit unit_id [integer] unit_location [character varying]
DATA: 1,'location1' 2,'location2' 3,'location3' STRUCTURE: tbloperator operator_id [integer] operator_name [character varying] DATA: 1,'operator1' 2,'operator2' 3,'operator3' 4,'operator4' 5,'operator5' 6,'operator6' STRUCTURE: tbloperatorschedule operator_schedule_id [bigint] operator_id [integer] {Foreign key tbloperator->operator_id} schedule_start_time [timestamp without time zone] schedule_end_time [timestamp without time zone] unit_id [bigint] {Foreign key tblunit->unit_id} DATA: 1,1,'2011-12-01 01:00:00','2011-12-01 02:00:00',1 2,5,'2011-12-01 02:30:00','2011-12-01 04:50:00',1 3,2,'2011-12-01 04:55:00','2011-12-01 10:20:00',1 4,1,'2011-12-01 03:00:00','2011-12-01 05:00:00',2 5,3,'2011-12-01 05:30:00','2011-12-01 09:50:00',2 6,4,'2011-12-01 09:55:00','2011-12-01 13:20:00',2 7,6,'2011-12-01 14:00:00','2011-12-01 18:00:00',2 8,5,'2011-12-01 06:30:00','2011-12-01 14:50:00',3 9,2,'2011-12-01 14:55:00','2011-12-01 20:20:00',3 STRUCTURE: tbldata data_id [bigint] event_time [timestamp without time zone] data_text [character varying] unit_id [bigint] {Foreign key tblunit->unit_id} DATA: 1,'2011-12-01 02:30:00','abc',1 2,'2011-12-01 06:28:00','abc',2 3,'2011-12-01 11:10:00','abc',3 4,'2011-12-01 21:30:00','abc',3 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. Resulting set: 1,'2011-12-01 02:30:00','abc',1,operator5 2,'2011-12-01 06:28:00','abc',2,operator3 3,'2011-12-01 11:10:00','abc',3,operator5 4,'2011-12-01 21:30:00','abc',3,NULL The sql query to create the tables and sample data is attached or can be seen at http://pastebin.com/Fy2t3H9S . Is it possible to get a condition within a left join or any other way to fetch the desired data. Amitabh
query.sql
Description: Binary data
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general