From: spinto@virtualslo.com
Subject: Help With complex join
Date: 14 Jul 2005 14:34:02 -0700
Organization: http://groups.google.com
Lines: 90
Message-ID: <1121376842.249209.127400@z14g2000cwz.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: z14g2000cwz.googlegroups.com; posting-host=24.176.166.121;
	posting-account=n9Ky5wwAAACFyFrT4lg6McY-U8mnvDVg
To: pgsql-sql@postgresql.org

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