Content-Type: text/plain;
  charset="iso-8859-1"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: Re: Concat and view - Re: create view error
Date: Mon, 7 Jul 2003 14:12:35 +0100
User-Agent: KMail/1.4.3
References: <200307071240.23936.gary.stainburn@ringways.co.uk>
	<200307071307.54935.dev@archonet.com>
	<200307071336.35933.gary.stainburn@ringways.co.uk>
In-Reply-To: <200307071336.35933.gary.stainburn@ringways.co.uk>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307071401.38483.gary.stainburn@ringways.co.uk>

On Monday 07 Jul 2003 1:36 pm, you wrote:
> On Monday 07 Jul 2003 1:07 pm, Richard Huxton wrote:
> > On Monday 07 Jul 2003 12:40 pm, Gary Stainburn wrote:
> > >       left outer join
> > >         (select lnumber from lnumbers) ln on ln.lnid = l.lid and
> >
> >                   ^^^^^^^                      ^^^^^^^
> >
> > > ERROR:  No such attribute or function ln.lnid
> >
> > Is is this?
>
> Yup, thanks to both of you for this answer.
>
> Is there any way to do this so that lnid is not visible in the resulting
> view?
>

I've managed to get the view I wanted by using sub-selects - as shown below, 
but I now have the 'lid' field showing in the resulting view three times (as 
lid, lnid and lnaid).  How can I remove lnid and lnaid from the result?

create view loco_dets as
  select * from locos l
      left outer join 
         lclass lc on lc.lcid = l.lclass
      left outer join
        lnumbers n on n.lnid = l.lid and n.lncurrent = true
      left outer join
        (select lnid as lnaid, concat(lnumber) as lnalternate from 
	  (select lnid, lnumber from lnumbers where lncurrent = false order by lnid, 
lnumber) alt
	  group by lnaid) na on na.lnaid = l.lid 
      left outer join
         company c on c.coid = lc.lcompany;


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