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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq