Postgresql query with left join and having

Question

Postgresql 9.1: I have a query that must return the values of a second table only if the aggregate function SUM of two columns is greater than zero.

This is the data:

Table a

id   
---
1
2
3

Table b

id fk(table a)
---------------
1  1
2  null
3  3

Table c

id  fk(table b) amount price
-----------------------------------
1   1             1     10   --positive
2   1             1     -10  --negative
3   3             2      5

As you can see, table b has some ids from table a, and table c can have 1 or more references to table b, table c is candidate to be retrieved only if the sum(amount * price ) > 0.

I wrote this query:

SELECT 
    a.id, b.id, SUM(c.amount * c.price) amount 
FROM
    tablea a
LEFT JOIN 
    tableb b ON b.fk = a.id
LEFT JOIN 
    tablec c ON c.fk = b.id
GROUP BY 
    a.id, b.id
HAVING 
   SUM(c.amount * c.price) > 0

But this query is not retrieving all rows from table a just the row 1 and I need the two rows. I understand this is happening because of the HAVING clause but I don't know how to rewrite it.

Expected result

a    b     sum
------------------
1    null   null  -- the sum of 1 * 10 (rows 1 and two) = 0 so its not retrieved.
2    null   null   -- no foreign key in second table
3    3      10     -- the sum of 2 * 5 (row 3) > 0 so it's ok.

Answer 1

Try this:

SELECT A.ID, B.ID, C.ResultSum
FROM TableA A
LEFT JOIN TableB B ON (B.FK = A.ID)
LEFT JOIN ( 
    SELECT FK, SUM(Amount * Price) AS ResultSum
    FROM TableC
    GROUP BY FK
) C ON (C.FK = B.ID) AND (ResultSum > 0)

See demo here.