From: spinto@virtualslo.com
To: pgsql-sql@postgresql.org
Subject: Help With complex join
Date: 14 Jul 2005 14:34:02 -0700
Message-ID: <1121376842.249209.127400@z14g2000cwz.googlegroups.com> (raw)
Hi all, got a question as how to approach a somewhat complicated join
query. The deal is I have three tables called attorney, lawOffice, and
law_office_employment. The attorney and lawOffice tables hold attorney
and lawOffice information respectively (obviously). The
law_office_employment table is meant to show historical periods of time
for which the attorney's worked for the different lawOffices. Here is
the create table statement for law_office_employment:
/*==============================================================*/
/* Table: LAW_OFFICE_EMPLOYMENT */
/*==============================================================*/
create table LAW_OFFICE_EMPLOYMENT (
ATTORNEYID IDENTIFIER not null,
LAWOFFICEID IDENTIFIER not null,
STARTDATE DATE not null,
constraint PK_LAW_OFFICE_EMPLOYMENT primary key (ATTORNEYID,
LAWOFFICEID, STARTDATE)
);
/*==============================================================*/
/* Index: LAW_OFFICE_EMPLOYMENT_PK */
/*==============================================================*/
create unique index LAW_OFFICE_EMPLOYMENT_PK on LAW_OFFICE_EMPLOYMENT (
ATTORNEYID,
LAWOFFICEID,
STARTDATE
);
/*==============================================================*/
/* Index: RELATION_46_FK */
/*==============================================================*/
create index RELATION_46_FK on LAW_OFFICE_EMPLOYMENT (
ATTORNEYID
);
/*==============================================================*/
/* Index: RELATION_48_FK */
/*==============================================================*/
create index RELATION_48_FK on LAW_OFFICE_EMPLOYMENT (
LAWOFFICEID
);
alter table LAW_OFFICE_EMPLOYMENT
add constraint FK_LAW_OFFI_RELATION__ATTORNEY foreign key
(ATTORNEYID)
references ATTORNEY (ATTORNEYID)
on delete restrict on update restrict;
alter table LAW_OFFICE_EMPLOYMENT
add constraint FK_LAW_OFFI_RELATION__LAW_OFFI foreign key
(LAWOFFICEID)
references LAWOFFICE (LAWOFFICEID)
on delete restrict on update restrict;
I can populate the law_office_employment so that it looks like this:
attorneyid | lawofficeid | startdate
------------+-------------+------------
1 | 1 | 2002-01-01
1 | 2 | 2002-02-01
1 | 1 | 2002-03-01
1 | 3 | 2002-04-01
My question is how to make a query that will display the PERIODS of
time for which an attorney worked for a particular office based on the
attorney then changing to a new law office and having the endDate of
the previous employment be the startDate of the new employment. I know
it sounds confusing but as an example I will show you what I would want
the query to return based on the information populated above.
attorneyid | lawofficeid | startdate | enddate
------------+-------------+------------+-----------
1 | 1 | 2002-01-01 | 2002-02-01
1 | 2 | 2002-02-01 | 2002-03-01
1 | 1 | 2002-03-01 | 2002-04-01
1 | 3 | 2002-04-01 | Present
I am pretty sure it involves joining the table with itself to cet the
cartesian product of all of the rows but I am at a loss for how to
construct the interval logic.
Any help would be greatly appreciated.
Sean Pinto
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spinto@virtualslo.com
Subject: Re: Help With complex join
In-Reply-To: <1121376842.249209.127400@z14g2000cwz.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox