From: gurkan@resolution.com
To: pgsql-sql@postgresql.org
Subject: Complex Informix OUTER
Message-ID: <1145392775.44454e87a8762@www.resolution.com>
Date: Tue, 18 Apr 2006 16:39:35 -0400 (EDT)
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: IMP/PHP IMAP webmail program 2.2.8

Hi all.
I have a fairly complicated Informix OUTER needs to be converted
to PostgreSQL and I cannot get around it. For me it is fairly complicated
Problem is inv_contracts OUTERS from three different tables
and two out of these three (invention, 'dbuser as con') needs to 
SELECT for data (invention.name as inv_name, con.lastname as con_lastname,
con.firstname as con_firstname)

Thanks for help.

First thought would be below but not allowed, inv_contracts needs to be renamed
and that wont be the correct conversion.

,invention     LEFT OUTER JOIN inv_contracts ON inv_contracts.inv_id = invid
,dbuser as con LEFT OUTER JOIN inv_contracts ON inv_contracts.con_id = con.id
,contractDef   LEFT OUTER JOIN inv_contracts ON inv_contracts.contract_id IN
(select id FROM contractDef WHERE phase_id = 2);

Possible sub-queries may not be the solution because I need to be able to some
selects from
'dbuser as con' and invention.

Thanks. Any help appriciated.

--ORIGINAL INFORMIX OUTER QUERY
SELECT user.username, user.firstname, user.lastname, add1, add2, city, 
stateDef.abbreviation as state, zip, invention.inv_number as invnum, 
invention.name as inv_name, con.lastname as con_lastname, con.firstname as
con_firstname
FROM user, ascpDef AS stateDef, address, invention, user as con
, OUTER inv_contracts 
WHERE 
address.type = 'User' AND 
address_id = 1 AND 
user.id = address.type_id AND 
state_id = stateDef.id AND 
invention.user_id = user.id AND 
invention.inv_number = '1994376-A' AND 
inv_contracts.inv_id = invention.id AND 
inv_contracts.con_id = con.id AND 
inv_contracts.contract_id IN (select id FROM contractDef WHERE phase_id = 2);

-------------------------------------------------
This mail sent through IMP: www.resolution.com