pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp With complex join
3+ messages / 3 participants
[nested] [flat]
* Help With complex join
@ 2005-07-14 21:34 spinto@virtualslo.com
0 siblings, 2 replies; 3+ messages in thread
From: spinto@virtualslo.com @ 2005-07-14 21:34 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help With complex join
@ 2005-07-15 06:43 Gnanavel S <s.gnanavel@gmail.com>
parent: spinto@virtualslo.com
1 sibling, 0 replies; 3+ messages in thread
From: Gnanavel S @ 2005-07-15 06:43 UTC (permalink / raw)
To: spinto@virtualslo.com <spinto@virtualslo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help With complex join
@ 2005-07-15 08:57 Richard Huxton <dev@archonet.com>
parent: spinto@virtualslo.com
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2005-07-15 08:57 UTC (permalink / raw)
To: spinto@virtualslo.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-07-15 08:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-14 21:34 Help With complex join spinto@virtualslo.com
2005-07-15 06:43 ` Gnanavel S <s.gnanavel@gmail.com>
2005-07-15 08:57 ` Richard Huxton <dev@archonet.com>
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