From: Gnanavel S <s.gnanavel@gmail.com>
To: spinto@virtualslo.com <spinto@virtualslo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help With complex join
Date: Fri, 15 Jul 2005 12:13:36 +0530
Message-ID: <eec3b03c050714234344fd5143@mail.gmail.com> (raw)
In-Reply-To: <1121376842.249209.127400@z14g2000cwz.googlegroups.com>
References: <1121376842.249209.127400@z14g2000cwz.googlegroups.com>
On 14 Jul 2005 14:34:02 -0700, spinto@virtualslo.com <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. 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.
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: s.gnanavel@gmail.com, spinto@virtualslo.com
Subject: Re: Help With complex join
In-Reply-To: <eec3b03c050714234344fd5143@mail.gmail.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