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?
Also, using examples from this list, I've created a concat function and
aggregate so that I can convert a number of rows to a comma delimited string.
I can then use this in a select as shown below, but what I can't work out is
how to put this into my join.
I want to include the second of the two selects shown below (the one with
'lncurrent = true' where clause) into my view (shown at bottom).
I can't work out where to put the where and group by clauses.
nymr=# select lnid, concat(lnumber) as lnalternate from lnumbers group by
lnid;lnid | lnalternate
------+-------------- 1 | 29 2 | 2392,65894 3 | 4277 4 | 80135 5 | 30926,926 6 | 45212 7 | 44767 8 |
60532 9 | 75014 10 | 75029 11 | 60007 12 | 25 278,D7628 13 | 08850,4518 14 | 62005,62012 15 | 24 061,D5061 16 |
45337 17 | 6619 18 | 64360,901 19 | 5 20 | 825 21 | 45157 22 | 76079 23 | 4771,60800 24 | 55019,D9019 25 |
D9009 26 | 08556,D3723
(26 rows)
nymr=# select lnid, concat(lnumber) as lnalternate from lnumbers where
lncurrent = false group by lnid;lnid | lnalternate
------+------------- 2 | 2392 5 | 926 12 | 25 278 13 | 08850 14 | 62012 18 | 64360 23 | 4771 24 | D9019 26 |
D3723
(9 rows)
nymr=#
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,
concat(lnumber)as lnalternate, lncurrent from lnumbers
) na on na.lnid = l.lid and na.lncurrent = false 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
