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