Converting query to view - duplicate fields

From: Richard Grosse <lists(at)keep-trying(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Converting query to view - duplicate fields
Date: 2004-03-09 22:40:33
Message-ID: 6.0.0.22.0.20040309223517.0351e328@192.168.5.1
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello,

Trying to convert the query below to a view. The problem is
despite it working as a query when trying to save it as a
view the database returns the error that the field tablealias.cmpname
is duplicated. (Which it is and has to be)

So how to a refer to the same table more than once and
get the data without getting the above error. (Basically it
is looking up different addresses depending on the codes
stored in mhdespatch table). Can I do this with a view, or
do I have to create several views and link them together.

Thanks

Richard
Postgresql 7.4.1,SuSE 8.1

Query Begins ==>

CREATE OR REPLACE VIEW public.test AS
SELECT mhdespatch.gdn, mhdespatch.date, mhdespatch.remarks,
mhdespatch.sp_instruct1,
mhdespatch.sp_instruct2, mhdespatch.sp_instruct3, tbldelivery.cmpname,
tbldelivery.adds1, tbldelivery.adds2, tbldelivery.adds3, tbldelivery.town,
tbldelivery.county, tbldelivery.pcode, tbldelivery.country, tbldelivery.tel1,
mhdespatch.del_ref, tblsender.cmpname, mhdespatch.send_ref,
tblhaulier.cmpname,
mhdespatch.haul_reg, mhdespatch.cov, tblhaulier.tel1
FROM mhdespatch
INNER JOIN tblfmna AS tblhaulier ON mhdespatch.haul_code = tblhaulier.code
INNER JOIN tblfmna AS tblsender ON mhdespatch.send_code = tblsender.code
INNER JOIN tblfmna AS tbldelivery ON mhdespatch.del_code = tbldelivery.code;

<=== Query Ends

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2004-03-09 23:14:26 Re: correlated multi-set update?
Previous Message Marty Scholes 2004-03-09 21:48:57 correlated multi-set update?