Thread: sub-select, view and sum()

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 rmilesfrom turns tleft outer join staff
don t.tfitter = d.sidleft outer join staff f on t.tccleaner = f.sidwhere r.rtid = t.tidorder 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     

On Mon, 6 Jan 2003, Gary Stainburn wrote:

> 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

Do you really need the outer where r.rtid=t.tid?  I would think that the
subselect where clause would already handle that for you.

On Monday 06 January 2003 6:31 pm, Stephan Szabo wrote:
> On Mon, 6 Jan 2003, Gary Stainburn wrote:
> > 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
>
> Do you really need the outer where r.rtid=t.tid?  I would think that the
> subselect where clause would already handle that for you.

Hi Stephan, 

Thanks for that. That was the problem.  I'd left it after trying to use a 
normal join in an earlier attempt.

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