From: Tomasz Myrta <jasiek@klaster.net>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: efficient count/join query
Date: Fri, 07 Feb 2003 11:48:47 +0100
Message-ID: <3E438F0F.5000301@klaster.net> (raw)
References: <200302071007.28697.gary.stainburn@ringways.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, gary.stainburn@ringways.co.uk
Subject: Re: efficient count/join query
In-Reply-To: <3E438F0F.5000301@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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