Re: Migration from SQLite Help (Left Join)
От | Nis Jørgensen |
---|---|
Тема | Re: Migration from SQLite Help (Left Join) |
Дата | |
Msg-id | f8jt1r$7sp$1@sea.gmane.org обсуждение исходный текст |
Ответ на | Migration from SQLite Help (Left Join) ("Mitchell Vincent" <ksoftware@gmail.com>) |
Ответы |
Re: Migration from SQLite Help (Left Join)
|
Список | 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
В списке pgsql-sql по дате отправления: