X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D15AA52D1A for ; Fri, 15 Jul 2005 03:43:49 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56880-06 for ; Fri, 15 Jul 2005 06:43:37 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.202]) by svr1.postgresql.org (Postfix) with ESMTP id 5395452BC7 for ; Fri, 15 Jul 2005 03:43:36 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id l1so315014nzf for ; Thu, 14 Jul 2005 23:43:37 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=pipCHG62gf1tGqRQjXMRKttWjTXSVZjvMWJw0QO8GRO6Szs3h90k+ETphuGBY16Rh2PpoDMyYmTSvzcfIrzbjE1WYrkIyMFiQQa6mVBBaJodAQONXGUSAf015YLdYNnySSB3hUE25LcDRdKSsa3ciI1dsfJQ5uHaw0mTRt36Zrk= Received: by 10.36.220.54 with SMTP id s54mr1162361nzg; Thu, 14 Jul 2005 23:43:36 -0700 (PDT) Received: by 10.36.59.12 with HTTP; Thu, 14 Jul 2005 23:43:36 -0700 (PDT) Message-ID: Date: Fri, 15 Jul 2005 12:13:36 +0530 From: Gnanavel S Reply-To: Gnanavel S To: "spinto@virtualslo.com" Subject: Re: Help With complex join Cc: pgsql-sql@postgresql.org In-Reply-To: <1121376842.249209.127400@z14g2000cwz.googlegroups.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8949_14718576.1121409816948" References: <1121376842.249209.127400@z14g2000cwz.googlegroups.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.77 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, RCVD_BY_IP, TO_ADDRESS_EQ_REAL, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200507/133 X-Sequence-Number: 22111 ------=_Part_8949_14718576.1121409816948 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 14 Jul 2005 14:34:02 -0700, spinto@virtualslo.com =20 wrote: >=20 > 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: >=20 > /*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/ > /* Table: LAW_OFFICE_EMPLOYMENT */ > /*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/ > 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) > ); >=20 > /*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/ > /* Index: LAW_OFFICE_EMPLOYMENT_PK */ > /*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/ > create unique index LAW_OFFICE_EMPLOYMENT_PK on LAW_OFFICE_EMPLOYMENT ( > ATTORNEYID, > LAWOFFICEID, > STARTDATE > ); >=20 > /*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/ > /* Index: RELATION_46_FK */ > /*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/ > create index RELATION_46_FK on LAW_OFFICE_EMPLOYMENT ( > ATTORNEYID > ); >=20 > /*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/ > /* Index: RELATION_48_FK */ > /*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/ > create index RELATION_48_FK on LAW_OFFICE_EMPLOYMENT ( > LAWOFFICEID > ); >=20 > alter table LAW_OFFICE_EMPLOYMENT > add constraint FK_LAW_OFFI_RELATION__ATTORNEY foreign key > (ATTORNEYID) > references ATTORNEY (ATTORNEYID) > on delete restrict on update restrict; >=20 > 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; >=20 >=20 >=20 > I can populate the law_office_employment so that it looks like this: >=20 > attorneyid | lawofficeid | startdate > ------------+-------------+------------ > 1 | 1 | 2002-01-01 > 1 | 2 | 2002-02-01 > 1 | 1 | 2002-03-01 > 1 | 3 | 2002-04-01 >=20 >=20 > 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. >=20 >=20 > 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=20 t2.attorneyid=3Dt1.attorneyid and t2.startdate > t1.start_date order by=20 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. >=20 > Any help would be greatly appreciated. >=20 > Sean Pinto >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >=20 --=20 with regards, S.Gnanavel Satyam Computer Services Ltd. ------=_Part_8949_14718576.1121409816948 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

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<= br> query.  The deal is I have three tables called attorney, lawOffic= e, and
law_office_employment.  The attorney and lawOffice tabl= es hold attorney
and lawOffice information respectively (obviously).&nbs= p; The
law_office_employment table is meant to show historical peri= ods of time
for which the attorney's worked for the different lawOffices. &nbs= p;Here is
the create table statement for law_office_employment:

/= *=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/
/* Table: LAW_OFFICE_EMPLOYMENT         =             &nb= sp;           */
/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/
create table LAW_OFFICE_= EMPLOYMENT (
ATTORNEYID        &= nbsp;  IDENTIFIER           not null,
LAWOFFICEID        &nb= sp; IDENTIFIER         &n= bsp; not null,
STARTDATE         = ;   DATE        &nbs= p;        not null,
constraint PK_LAW_OFFICE_EMPLOYMENT primary key (ATTORNEYID,LAWOFFICEID, STARTDATE)
);

/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D*/
/* Index: LAW_OFFICE_EMPLOYMENT_PK        &nb= sp;            =          */
/*=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D*/
create unique index LAW_OFFICE_EMPLOYMENT_PK = on LAW_OFFICE_EMPLOYMENT (
ATTORNEYID,
LAWOFFICEID,
STARTDATE
);

/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D*/
/* Index: RELATION_46_FK          &= nbsp;           &nbs= p;            &= nbsp;    */
/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= */
create  index RELATION_46_FK on LAW_OFFICE_EMPLOYMENT (
= ATTORNEYID
);

/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/
/* Index: RELATION_48_FK          &= nbsp;           &nbs= p;            &= nbsp;    */
/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= */
create  index RELATION_48_FK on LAW_OFFICE_EMPLOYMENT (
= LAWOFFICEID
);

alter table LAW_OFFICE_EMPLOYMENT
   add constraint FK_LAW_OFFI_RELATION__ATTORNEY foreign key<= br>(ATTORNEYID)
      references ATTORNEY = (ATTORNEYID)
      on delete restrict on u= pdate restrict;

alter table LAW_OFFICE_EMPLOYMENT
   ad= d constraint FK_LAW_OFFI_RELATION__LAW_OFFI foreign key
(LAWOFFICEID)
      references LAWOFFI= CE (LAWOFFICEID)
      on delete restrict = on update restrict;



I can populate the law_office_employment= so that it looks like this:

attorneyid | lawofficeid | startdate
------------+-------------+------------
    &nbs= p;     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. &n= bsp;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=3Dt1.attorneyid and  t2.startdate > t1.start_date order by t2.startdate limit 1) from LAW_OFFICE_EMPLOYMENT t1;

I am p= retty sure it involves joining the table with itself to cet the
cartesia= n product of all of the rows but I am at a loss for how to
construct the interval logic.

Any help would be greatly apprecia= ted.

Sean Pinto


---------------------------(end of broadc= ast)---------------------------
TIP 5: don't forget to increase your fre= e space map settings



--
with regards,
S.Gnanavel
Sa= tyam Computer Services Ltd. ------=_Part_8949_14718576.1121409816948--