Help With complex join
От | spinto@virtualslo.com |
---|---|
Тема | Help With complex join |
Дата | |
Msg-id | 1121376842.249209.127400@z14g2000cwz.googlegroups.com обсуждение исходный текст |
Ответы |
Re: Help With complex join
(Gnanavel S <s.gnanavel@gmail.com>)
Re: Help With complex join (Richard Huxton <dev@archonet.com>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: