Re: efficient count/join query

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