Conditional left join

From: Amitabh Kant <amitabhkant(at)gmail(dot)com>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Conditional left join
Date: 2011-12-01 12:16:38
Message-ID: CAPTAQBJ7xUO=aPhx24PqeZTTsYKw+mZ_UN9pNKS2YbqGHDUAJw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Attachment Content-Type Size
query.sql application/octet-stream 2.1 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2011-12-01 12:27:09 Re: Conditional left join
Previous Message tamanna madaan 2011-12-01 11:57:50 Re: psql query gets stuck indefinitely