Content-Type: text/plain;
  charset="iso-8859-1"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: Tomasz Myrta <jasiek@klaster.net>
Subject: Re: efficient count/join query
Date: Fri, 7 Feb 2003 12:57:47 +0000
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <200302071007.28697.gary.stainburn@ringways.co.uk>
	<3E438F0F.5000301@klaster.net>
	<200302071209.55330.gary.stainburn@ringways.co.uk>
In-Reply-To: <200302071209.55330.gary.stainburn@ringways.co.uk>
MIME-Version: 1.0
Message-Id: <200302071257.48028.gary.stainburn@ringways.co.uk>
Content-Transfer-Encoding: quoted-printable

On Friday 07 Feb 2003 12:09 pm, Gary Stainburn wrote:
> On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote:
> > Gary Stainburn wrote:
> > > Hi folks,
> > >
> > > I've got two tables, first a history table containing tallies for
> > > staff/jobs prior to going live, and second a roster table showing dat=
e,
> > > diagram, job with one record per person per job per day.  the tables
> > > are:
> > >
> > > create table history (
> > > hsid	int4 not null references staff(sid),
> > > hjid	int4 not null references jobs(jid),
> > > hcount	int4,
> > > primary key (hsid,hjid));
> > >
> > > create table roster (
> > > rodate	date not null,
> > > rogid	int4 not null references diagrams(gid),
> > > rojid	int4 not null references jobs(jid),
> > > rosid	int4 references staff(sid),
> > > primary key (rodate, rogid, rojid));
> > >
> > > What's the best/quickest/cheapest way to create a view in the format =
of
> > > the history table but including the details from the roster table for
> > > all records prior to today.
> > >
> > > I've been looking at some form of sub-select/join scheme but as some
> > > will only exist on the history and some will only exist on the roster
> > > while many will exist on both.
> >
> > Hello again.
> >
> > What if they exists in both tables - you need only one row result?
> > If yes, you should use FULL OUTER JOIN and COALESCE.
> >
> > select
> >  coalesce(hjid,rjid) as jid,
> >  coalesce(hsid,rsid) as sid,
> >  hcount,
> >  rodate,
> >  rogid
> > from
> >  history
> >  full outer join roster on (hjid=3Drjid and hsid=3Drosid)
> >
> > Using other names for the same field in other tables comes again -
> > If you have the same name for jid and sid, you wouldn't need coalesce.
> >
> > Regards,
> > Tomasz Myrta
>
> Hi Tomasz,
>
> I don't think you understand what I mean.
>
> The history table could be thought of as the following SQL statement if t=
he
> data had actually existed. This table actually represents a manually input
> summary of the pre-computerised data.
>
>  insert into history
>     select rosid, rojid, count(*) from roster_staff group by rssid, rsgsi=
d;
>
> If I have a history of
>
>  hsid | hjid | hcount
> ------+------+--------
>     1 |    2 |      3
>     1 |    3 |      1
>     5 |    5 |      4
>     6 |    5 |      3
>     9 |    4 |      4
>    14 |    5 |      4
>
> and I have a roster of
>
>   rodate   | rogid | rojid | rosid
> -----------+-------+-------+-------
> 2003-02-15 |   1   |   2   |   1
> 2003-02-15 |   1   |   5   |   5
> 2003-02-16 |   1   |   5   |   1
>
> I want my view to show
>
>  hsid | hjid | hcount
> ------+------+--------
>     1 |    2 |      4
>     1 |    3 |      1
>     1 |    5 |      1
>     5 |    5 |      5
>     6 |    5 |      3
>     9 |    4 |      4
>    14 |    5 |      4


Thinking about it, I'm not wanting to perform a join as such, but a merge o=
f=20
the two selects below, then some form of group by to sum() the two counts.

    select rosid as sid, rojid as jid, count(*) as count
                  from roster group by sid, jid order by sid, jid;
    select hsid as sid, hjid as jid, hcount as count=20=20
                  from history order by sid, jid;

so that=20

1	2	1
1	3	2

and=20

1	3	1
1	4	2

becomes

1	2	1
1	3	3
1	4	2

--=20
Gary Stainburn
=20
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000=20=
=20=20=20=20