pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsub-select, view and sum()
3+ messages / 2 participants
[nested] [flat]
* sub-select, view and sum()
@ 2003-01-06 17:29 Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-01-06 18:31 ` Re: sub-select, view and sum() Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: Gary Stainburn @ 2003-01-06 17:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: sub-select, view and sum()
2003-01-06 17:29 sub-select, view and sum() Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2003-01-06 18:31 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-06 22:29 ` Re: sub-select, view and sum() Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2003-01-06 18:31 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: sub-select, view and sum()
2003-01-06 17:29 sub-select, view and sum() Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-01-06 18:31 ` Re: sub-select, view and sum() Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-01-06 22:29 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Gary Stainburn @ 2003-01-06 22:29 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-01-06 22:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-06 17:29 sub-select, view and sum() Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-01-06 18:31 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-01-06 22:29 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
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