DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=Wt86b6mYO1WqnfVl8KzhkBLtDBhtkHVketiBipLLYh7p/0FAtjohT3gZHzsmJ2gMQG81aXpOQmcQepqUF15cC3fO5GcxntbhOdHHXGak9DeT4W277uaCAfbcMEsDCcyalvcpwEafXhFrhofJCt1kK/FlajwJpYx6DB6xCtg6PXQ=
Message-ID: <173c46830707291928p1b701e31u444782ee3e439c96@mail.gmail.com>
Date: Sun, 29 Jul 2007 22:28:10 -0400
From: "Mitchell Vincent" <ksoftware@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Migration from SQLite Help (Left Join)
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

SELECT c.customer_id as customer_id,c.customer_number as customer_number,
c.customer_name as customer_name,c.customer_status as
customer_status,cat.category_name as category_name,
c.bill_state as bill_state, coalesce(ctots.balance_due, 0.00) as
balance FROM customers as c,
customer_categories as cat
left join
(Select customer_id as cid, coalesce(sum(balance_due),0) as balance_due
FROM invoice_master WHERE status = 'Pending' group by cid) ctots on
ctots.cid = c.customer_id
where cat.category_id = c.category_id AND customer_name
LIKE lower('%%')  AND (c.customer_status = 'Terminated' OR
c.customer_status = 'Active' or c.customer_status = 'Inactive')
ORDER BY c.customer_number DESC  LIMIT 25

I know, it's a beast, but I'm porting an application from SQLite to
PostgreSQL and this is the only query that isn't working properly in
PostgreSQL.

The error is "ERROR: invalid reference to FROM-clause entry for table "c"
Hint: There is an entry for table "c", but it cannot be referenced
from this part of the query.
Character: 475" - it's the "on ctots.cid = c.customer_id " part that's breaking.

Is there any way to accomplish the same thing in PG?

-- 
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net