Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 6116C9FB259
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  6 Nov 2006 20:31:11 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 65517-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Nov 2006 00:31:08 +0000 (UTC)
X-Greylist: delayed 00:20:33.361492 by SQLgrey-
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 1E30F9FA12F
	for <pgsql-sql@postgresql.org>; Mon,  6 Nov 2006 20:31:08 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from resolution.com (unknown [65.18.201.170])
	by svr4.postgresql.org (Postfix) with ESMTP id 386665AF8A2
	for <pgsql-sql@postgresql.org>; Tue,  7 Nov 2006 00:10:33 +0000 (GMT)
Received: (from resolutio@localhost)
	by resolution.com (8.12.10/8.11.2) id kA70AVOr014835
	for pgsql-sql@postgresql.org; Mon, 6 Nov 2006 19:10:31 -0500
From: gurkan@resolution.com
X-Authentication-Warning: resolution.com: resolutio set sender to
	gurkan@resolution.com using -f
To: pgsql-sql@postgresql.org
Subject: converting Informix outer to Postgres
Message-ID: <1162858231.454fcef749413@www.resolution.com>
Date: Mon, 06 Nov 2006 19:10:31 -0500 (EST)
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
X-Originating-IP: 70.238.208.142
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/94
X-Sequence-Number: 26746

Hi all,
I have been working on this Informix SQL query which has an outer join.
I have attached Informix query and my "supposedly" solution to this query
but I cannot get the same count. I appreciate for any help.
Thanks.

--Informix query
select count(u.id)
from user u, invention i, inv_contracts ic, inv_milestones im1, milestonedef mdef1,
OUTER inv_milestones im2,
milestonedef mdef2
where u.id = i.user_id and
ic.inv_id = i.id and
ic.contract_id = mdef1.contract_id and
im1.inv_id = i.id and
mdef1.id = im1.milestone_id and
im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and
ic.contract_id = mdef2.contract_id and
im2.inv_id = i.id and
mdef2.id = im2.milestone_id and
im1.datereceived IS NULL

--Postges query
select count(u.id)
from dbuser u, inv_contracts ic, inv_milestones im1, milestonedef mdef1,
--OUTER inv_milestones im2,
milestonedef mdef2 LEFT OUTER JOIN inv_milestones im2 ON mdef2.id = im2.milestone_id
LEFT OUTER JOIN invention i ON im2.inv_id = i.id
where u.id = i.user_id and 
ic.inv_id = i.id and 
ic.contract_id = mdef1.contract_id and 
im1.inv_id = i.id and 
mdef1.id = im1.milestone_id and 
im1.datesent BETWEEN '2005-05-05' AND '2005-05-10' and
ic.contract_id = mdef2.contract_id and
--im2.inv_id = i.id and 
--mdef2.id = im2.milestone_id and 
im1.datereceived IS NULL

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