I have 2 tables:
T1 (id, flag1)
T2 (id, amount, date, flag2, t1_id);
I have the following query:
SELECT T1.id, ROUND(COALESCE(SUM(T2.amount), 0), 2) AS spent_amount
FROM T1
LEFT JOIN T2 ON T2.t1_id = T1.id
WHERE T2.date <= '2014-01-01' AND T2.flag2 = 't' AND T1.flag1 = 't'
GROUP BY T1.id
The problem is that I want to have a row in the result such as: id = 123, spent_amount = 0
in case where I have an entrance in T1
, but it has no connected rows in T2
.
Having a WHERE clause on your T2 it will filter out all NULLS:
SELECT T1.id, ROUND(COALESCE(SUM(T2.amount), 0), 2) AS spent_amount
FROM T1
LEFT JOIN T2
ON T2.t1_id = T1.id
AND T2.date <= '2014-01-01'
AND T2.flag2 = 't'
WHERE T1.flag1 = 't'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With