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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
