On Mon, 7 Jul 2003, Gary Stainburn wrote:
> Hi folks,
>
> I know I'm missing something blindingly obvious, can someone point it out to
> me please.
>
> create table locos ( -- Locos table - contains details of locos
> lid int4 default nextval('loco_lid_seq'::text) unique not null,
> lclass int4 references lclass(lcid), -- Loco Class
> lbuilt date, -- Date off-shed
> lcme int4 references cme(cmid), -- Chief Mechanical Engineer
> lname character varying(30), -- Name of Loco
> lcomments text -- free text comments
> );
> NOTICE: CREATE TABLE / UNIQUE will create implicit index 'locos_lid_key' for
> table 'locos'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> create table lnumbers ( -- alternate loco numbers
> lnid int4 not null references locos(lid),
> lnumber character varying(10),
> lncurrent bool,
> primary key (lnid, lnumber)
> );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lnumbers_pkey'
> for table 'lnumbers'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> create view loco_dets as
> select * from locos l
> left outer join
> (select * from lclass) lc on lc.lcid = l.lclass
> left outer join
> (select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent ^^^
select also lnid
> = true
> left outer join
> (select * from company) c on c.coid = lc.lcompany;
> ERROR: No such attribute or function ln.lnid
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr
