Thread: Help With complex join

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

spinto@virtualslo.com wrote:
> 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.

But it doesn't. Looking below, it shows the date they started in each 
law office, not the period they worked there.

In fact, you can't capture a period of unemployment/sabbatical using 
just this table.
> 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)
> );

Make your life easier and have start and end-dates. Oh, you might want a 
"finished-here" flag too to indicate the end-date can be checked.

--  Richard Huxton  Archonet Ltd