Problem with NULL values

[Vack]

I have two tables. grtbk and gbkmut. both link by reknr(Account Number)

grtbk holds all of my General Ledger Accounts.
gbkmut holds trx's for each account.

When I have a month with no transactions I still want a record returned that shows 0.

I have the following Code:
SELECT TOP (100) PERCENT dbo.gbkmut.reknr, ISNULL(SUM(dbo.gbkmut.bdr_hfl), 0) AS Amount, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum) AS Year,
MONTH(dbo.gbkmut.datum) AS Month
FROM dbo.gbkmut LEFT OUTER JOIN
dbo.grtbk ON dbo.gbkmut.reknr = dbo.grtbk.reknr
GROUP BY dbo.gbkmut.reknr, dbo.grtbk.bal_vw, YEAR(dbo.gbkmut.datum), MONTH(dbo.gbkmut.datum), dbo.gbkmut.transtype
HAVING (dbo.grtbk.bal_vw = 'W') AND (dbo.gbkmut.transtype <> 'V')
ORDER BY 'Year', Month

--------------------------------------------------------------
Which returns the following. The ISNULL() does not seem to be working. I still want to see month 4 and 5 with a 0 amount.
reknr Amount bal_vw YEAR MONTH
41401 -47.79 W 2009 1
41401 -47.31 W 2009 2
41401 -23.3 W 2009 3
41401 22.87 W 2009 6

[Delerna]

grtbk has all records and gbkmut may or may not have records
You need to join the "might have records" to the "always has records"


You have left joined gbkmut (might Have Records) to grtbk (always has records).
It should be the other way around.

SELECT blah blah
FROM grtbk
LEFT JOIN gbkmut on grtbk.reknr = gbkmut.reknr 
blah blah
 

Notice above the "always has records" table is on the left so we use a LEFT join


Alternatively, as your query is now, you could use a RIGHT join instead,
because the "always has records" table is on the right