Re: efficient count/join query

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 date,
> > > 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=rjid and hsid=rosid)
> >
> > 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 the
> 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, rsgsid;
>
> 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 of
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
from history order by sid, jid;

so that

1 2 1
1 3 2

and

1 3 1
1 4 2

becomes

1 2 1
1 3 3
1 4 2

--
Gary Stainburn

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