Why won't this query work correctly?

Question

My table MasterSaleslooks 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 the table Categorylooks like

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

my request 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 as follows:

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 that I get is as follows:

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 JOINinstead LEFT JOINand switching the table to FROM, but the result is still the same. can someone help explain to me why it will not work properly?

PS: I am using SQL Server 2005 (if that 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

Answer 2

. , MasterSales. , . , , date. cte .

;with control_table as (
  select distinct SalesDate, ID
  from MasterSales ms
  cross join Category c
  where ms.SalesDate between '1/1/2000' AND '1/2/2000'
  )


select ct.SalesDate, c.Name as Category, coalesce(sum(ms.Total),0) as Total
from control_table ct
inner join Category c
  on ct.ID = c.ID
left join MasterSales ms
  on ct.SalesDate = ms.SalesDate
  and ct.ID = ms.Category
group by ct.SalesDate, c.Name
order by ct.SalesDate asc, c.Name asc

FIDDLE

Answer 3

, . :

SELECT AAA.SalesDate,AAA.Name,ISNULL(BBB.TotalSales,0) as TotalSales  
FROM (
    (SELECT m.SalesDate, c.Name
    FROM MasterSales m,Category c
    WHERE m.SalesDate BETWEEN '1/1/2000' AND '1/2/2000'
    GROUP BY m.SalesDate,c.Name) AAA

    LEFT JOIN 

    (SELECT m.SalesDate,c.Name, SUM(ISNULL(m.Total,0)) AS TotalSales
    FROM MasterSales m,Category c
    WHERE m.SalesDate BETWEEN '1/1/2000' AND '1/2/2000'
    AND c.ID=m.Category 
    GROUP BY m.SalesDate,c.Name) BBB 
    on AAA.SalesDate=BBB.SalesDate AND AAA.Name=BBB.Name) 

:

(AAA) SalesDate Category.

(BBB) ​​ SalesDate, Category TotalSales.

(AAA BBB) SalesDate Category.

!!!. . SQL Fiddle.

Answer 4

: sam yi logic

.

;WITH control_table AS (
      SELECT DISTINCT SalesDate, Name
        FROM MasterSales ms
             CROSS JOIN Category c
       )

SELECT d.SalesDate,
       c.name,
       COALESCE(sum(m.total),0) totalsales
  FROM control_table d
       LEFT OUTER JOIN Category c
            ON d.Name = c.name
       LEFT OUTER JOIN MasterSales m
            ON (c.id = m.category
                AND d.SalesDate = m.salesdate)
WHERE d.SalesDate BETWEEN '1/1/2000' 
                      AND '1/2/2000'
GROUP BY 
       d.SalesDate,
       c.name
ORDER BY 
       d.SalesDate,
       c.name;

SQLFiddle

Answer 5

SELECT cj.Date, cj.Name, IsNull(m.Total,0) Total
FROM (SELECT d.Date, c.Name, c.Id
    FROM (SELECT DISTINCT SalesDate Date 
          FROM MasterSales) d, Category c) cj
LEFT JOIN MasterSales m ON m.SalesDate = cj.Date 
  AND cj.Id = m.Category

http://www.sqlfiddle.com/#!3/d1344/3

Add a date between conditions

And I'm sure there were better ways to do this.

Greetings