[SOLVED] All values from first table, even if nothing matchs on other tables

All values from first table, even if nothing matchs on other tables

I have three tables (shown below with the relevant columns)

Table SALESSaleCodes

"SaleCode" VARCHAR(2) COLLATE "ANSI" DEFAULT '',
"Description" VARCHAR(25) COLLATE "ANSI" DEFAULT ''

Table INVOIHeader

"DocNum" INTEGER DEFAULT 0,
"AcctDate" DATE

Table INVOIItems

"DocNum" INTEGER DEFAULT 0,
"SaleCode" VARCHAR(2) COLLATE "ANSI" DEFAULT '',
"Cost" DECIMAL(19,2) DEFAULT 0.00,
"SellPrice" DECIMAL(19,2) DEFAULT 0.00,
"LaborEach" DECIMAL(19,2) DEFAULT 0.00,
"QtySold" DECIMAL(19,2) DEFAULT 0.00

I wrote this statement which returns all salecodes in SALESSaleCodes and the respective columns I am trying to get including those where count is zero and the sums are NULL.

SELECT a.SaleCode, a.Description, COUNT(b.SaleCode) AS Count, SUM(b.SellPrice * b.QtySold) AS Parts,
SUM(b.LaborEach * b.QtySold) AS Labor, SUM(b.Cost * b.QtySold) AS Cost
FROM SALESSaleCodes a
LEFT JOIN INVOIItems b ON a.SaleCode = b.SaleCode
GROUP BY a.SaleCode
ORDER BY a.SaleCode

What I need is one that takes into account the AcctDate from INVOIHeader where that date is between two given dates.

I tried this

SELECT a.SaleCode, a.Description, COUNT(b.SaleCode) AS Count, SUM(b.SellPrice * b.QtySold)     AS Parts,
SUM(b.LaborEach * b.QtySold) AS Labor, SUM(b.Cost * b.QtySold) AS Cost
FROM SALESSaleCodes a
LEFT JOIN INVOIItems b ON a.SaleCode = b.SaleCode
LEFT JOIN INVOIHeader c ON b.DocNum = c.DocNum
WHERE c.AcctDate BETWEEN
CAST('2013-10-01' AS DATE) AND
CAST('2013-10-31' AS DATE)
GROUP BY b.SaleCode
ORDER BY b.SaleCode

But what this returns is only the SaleCodes that fall in that range and it is not giving me all of the SaleCodes from SALESSaleCodes .

What statement would I need to get all of the SaleCodes whether they are within this range or not and only the sums and counts that are in this date range. Other sums and counts would be NULL.

I am working in ElevateDB which uses SQL 2003 standard (ANSI ISO/IEC 9075:2003). I know most of you are Oracle, SQLServer, or MySQL users but I thought I would ask on here and see what you guys could come up with.

Solution

...    
    WHERE (
      (c.AcctDate BETWEEN CAST('2013-10-01' AS DATE) AND CAST('2013-10-31' AS DATE))
      OR c.AcctDate IS NULL
    )
...