From: Nis Jørgensen <nis@superlativ.dk>
To: pgsql-sql@postgresql.org
Subject: Re: Migration from SQLite Help (Left Join)
Date: Mon, 30 Jul 2007 07:30:18 +0200
Message-ID: <f8jt1r$7sp$1@sea.gmane.org> (raw)
In-Reply-To: <173c46830707291928p1b701e31u444782ee3e439c96@mail.gmail.com>
References: <173c46830707291928p1b701e31u444782ee3e439c96@mail.gmail.com>
Mitchell Vincent skrev:
> 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
The problem seems to be that you expect
SELECT a
FROM b,c LEFT JOIN d
to be interpreted as
SELECT a
FROM (b CROSS JOIN c) LEFT JOIN d
whereas it is translated by postgresql as
SELECT a
FROM b CROSS JOIN (c LEFT JOIN d)
There are many ways to fix this - I would suggest moving the join
condition into the FROM-clause:
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 INNER JOIN
customer_categories as cat ON cat.category_id = c.category_id
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 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
In fact, I believe you could remove the subquery as well:
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(sum(im.balance_due, 0.00) as
balance FROM customers as c INNER JOIN
customer_categories as cat ON cat.category_id = c.category_id
LEFT JOIN invoice_master im ON im.status = 'Pending' AND im.cid =
c.customer_id
WHERE 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nis@superlativ.dk
Subject: Re: Migration from SQLite Help (Left Join)
In-Reply-To: <f8jt1r$7sp$1@sea.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox