Content-Type: text/plain;
  charset="us-ascii"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: sub-select, view and sum()
Date: Mon, 6 Jan 2003 17:29:07 +0000
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200301061729.07459.gary.stainburn@ringways.co.uk>

Hi folks,

I've got 3 tables (plus others), and want to create a view joining them.  
Below are the two main tables and the view I'm trying to create.  Anyone, got 
any idea how I need to word the 'create view'

create table turns (		-- Turns Table. Hold details of my turns
tid     	int4 default nextval('turns_tid_seq'::text) unique not null,
tdate		date,			-- date of turn
tseq		int4,			-- sheet reference number
ttype		char references ttypes(ttid),	-- Turn type
tfitter		int4 references staff(sid),	-- fitter or driver
tccleaner	int4 references staff(sid),	-- charge cleaner or fireman
tcomments	text			-- free type description of turn
);
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'turns_tid_key' for 
table 'turns'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create table rides (		-- work details list by turn/category + mileage
rtid		int4 references turns(tid), -- TID of associated turn
rlid		int4 references locos(lid), -- LID of associated engine
rcid		character references categories(cid), -- CID of category
rmiles		int4			-- miles travelled on ride-out
);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create unique index "rides_index" on rides using btree ("rtid", "rlid", 
"rcid");
CREATE
create view turn_details as
  select t.*, d.sid as dsid, d.sname as dname, 
              f.sid as fsid, f.sname as fname,
	      (select sum(r.rmiles) as rmiles from rides r where r.rtid = tid) 
	        as rmiles
	from turns t
	left outer join staff d on t.tfitter = d.sid
	left outer join staff f on t.tccleaner = f.sid
	where r.rtid = t.tid
	order by tdate;
ERROR:  Relation 'r' does not exist

-- 
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