Join two table with sum that retrieve null value

Question

I have two tables.

TABLE A :
I_NO , I_NAME
1      A
2      B
3      C
4      D
5      F
TABLE B :
THE_DATE , I_NO , THE_MONY
01-JAN-2017  1       50
01-FEB-2017  2       50
01-APR-2017  3       50
01-SEP-2017  2       50
01-APR-2017  1       50

I would like to create a query That Joining the two tables in which gives me all the I_NO from TABLE A and the sum of THE_MONY with DATE <= 01-FEB-2017 If there is a null value in the sum the query should give me 0 for the sum. The result should be like the followings:

1      50
2      50
3      0
4      0
5      0

Answer 1

If its mysql i would do like left join and then group by.

SELECT A.I_NO, SUM(IsNull(B.THE_MONY, 0)) as 'THE_MONY 
FROM A
LEFT JOIN B ON A.I_NO = B.I_NO
WHERE B.THE_DATE <= '01-FEB-2017' GROUP BY A.I_NO.

Something like this. hope it helps you.