Content-Type: text/plain;
  charset="iso-8859-1"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Subject: Re: sub-select, view and sum()
Date: Mon, 6 Jan 2003 22:29:47 +0000
User-Agent: KMail/1.4.3
Cc: <pgsql-sql@postgresql.org>
References: <20030106102933.K47795-100000@megazone23.bigpanda.com>
In-Reply-To: <20030106102933.K47795-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200301062229.47578.gary.stainburn@ringways.co.uk>

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