Message-ID: <3E438F0F.5000301@klaster.net>
Date: Fri, 07 Feb 2003 11:48:47 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: efficient count/join query
References: <200302071007.28697.gary.stainburn@ringways.co.uk>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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