DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=tqKcA2gcrSGWRpYGzzIYpq3gGD3HqNb9baHTlgV/XjJtPGPZ9SckRa6X2d0NA75xQxl+8rBZotx4QWpCzH8UMzYoiLkfGjh9M8vYO8Y8q1xbi+WeYV2gIzAmswsenJWQRJlk7Z4Q67ArOsloHJi4AeC9/DRsXNQYZOMa6Erx2vQ=
	;
Date: Mon, 6 Nov 2006 16:58:39 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: converting Informix outer to Postgres
To: gurkan@resolution.com, pgsql-sql@postgresql.org
In-Reply-To: <1162858231.454fcef749413@www.resolution.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <429904.33409.qm@web31812.mail.mud.yahoo.com>


--- gurkan@resolution.com wrote:

> 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

Is there a reason that these two lines are commented out in the postgresql query?

Regards,

Richard Broersma Jr.