Message-Id: <6.0.0.22.0.20040309223517.0351e328@192.168.5.1>
Date: Tue, 09 Mar 2004 22:40:33 +0000
To: pgsql-sql@postgresql.org
From: Richard Grosse <lists@keep-trying.com>
Subject: Converting query to view - duplicate fields
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

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