Re: Help With complex join

On 14 Jul 2005 14:34:02 -0700, spinto(at)virtualslo(dot)com <spinto(at)virtualslo(dot)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. 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

Try this,
select t1.*,(select t2.startdate from LAW_OFFICE_EMPLOYMENT t2 where
t2.attorneyid=t1.attorneyid and t2.startdate > t1.start_date order by
t2.startdate limit 1) from LAW_OFFICE_EMPLOYMENT t1;

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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.