why won't this query work properly?

Question

my MasterSales table looks like this

SalesDate | Category | Total
----------------------------- 
1/1/2000    01          100 
1/1/2000    02          110
1/2/2000    01          80
1/2/2000    03          20

and my Category table looks like this

ID | Name
----------
01 | A
02 | B
03 | C
04 | D

my query looks like this:

SELECT m.SalesDate, c.Name, SUM(ISNULL(m.Total,0)) AS TotalSales
FROM MasterSales m
LEFT JOIN Category c ON c.ID = m.Category
WHERE m.SalesDate BETWEEN '1/1/2000' AND '1/2/2000'

the result I want is like this:

SalesDate | Name | TotalSales
------------------------------
1/1/2000    A      100
1/1/2000    B      110
1/1/2000    C      0
1/1/2000    D      0
1/2/2000    A      80
1/2/2000    B      0
1/2/2000    C      20
1/2/2000    D      0

but the result I get looks this:

SalesDate | Name | TotalSales
------------------------------
1/1/2000    A      100
1/1/2000    B      110
1/2/2000    A      80
1/2/2000    C      20

I already tried using RIGHT JOIN instead of LEFT JOIN and switching the table on FROM clause but the result is still the same. can anyone help explain to me why it won't work properly?

P.S. : I'm using SQL Server 2005 (if it matters)

Answer 1

and here is my answer

WITH MasterSales (SalesDate, Category, Total) AS (
  SELECT       '1/1/2000','01',100
  UNION SELECT '1/1/2000','02',110
  UNION SELECT '1/2/2000','01',80
  UNION SELECT '1/2/2000','03',20
), Category (ID, Name) AS (
  SELECT       '01','A'
  UNION SELECT '02','B'
  UNION SELECT '03','C'
  UNION SELECT '04','D'
), getDates AS (
  SELECT DISTINCT SalesDate 
  FROM MasterSales 
  WHERE SalesDate BETWEEN '1/1/2000' AND '1/2/2000'
)

SELECT gD.SalesDate, C.Name, SUM(ISNULL(MS.Total,0)) AS TotalSales
FROM getDates AS gD
CROSS JOIN Category AS C
LEFT JOIN MasterSales AS MS 
    ON MS.Category = C.ID
    AND MS.SalesDate = gD.SalesDate 
GROUP BY gD.SalesDate, C.Name