pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMigration from SQLite Help (Left Join)
3+ messages / 3 participants
[nested] [flat]
* Migration from SQLite Help (Left Join)
@ 2007-07-30 02:28 Mitchell Vincent <ksoftware@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mitchell Vincent @ 2007-07-30 02:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Migration from SQLite Help (Left Join)
@ 2007-07-30 05:30 Nis Jørgensen <nis@superlativ.dk>
parent: Mitchell Vincent <ksoftware@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nis Jørgensen @ 2007-07-30 05:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Migration from SQLite Help (Left Join)
@ 2007-07-31 05:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nis Jørgensen <nis@superlativ.dk>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2007-07-31 05:34 UTC (permalink / raw)
To: Nis Jørgensen <nis@superlativ.dk>; +Cc: pgsql-sql
=?ISO-8859-1?Q?Nis_J=F8rgensen?= <nis@superlativ.dk> writes:
> 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
The depressing part of this report is that it sounds like sqlite has
emulated this bit of mysql brain-damage ...
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-07-31 05:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-30 02:28 Migration from SQLite Help (Left Join) Mitchell Vincent <ksoftware@gmail.com>
2007-07-30 05:30 ` Nis Jørgensen <nis@superlativ.dk>
2007-07-31 05:34 ` Tom Lane <tgl@sss.pgh.pa.us>
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