SQL query help

Hello-

I have written the SQL query below as a product sales report, and it works perfectly, except for one thing....
I need for the query to also display those products which sold a quantity of zero (0) during the given date range, but I can't seem to figure it out....

Can someone point me in the right direction please?

SELECT    dbo.OrderItem.ProductId, 
          dbo.Product.SKU, 
          dbo.Product.Name, 
          SUM(dbo.OrderItem.Quantity) AS [# Units Sold], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax) AS [Total Sales Revenue], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Product Cost], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax - dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Profit]

FROM      dbo.[Order] INNER JOIN
          dbo.OrderItem ON dbo.[Order].Id = dbo.OrderItem.OrderId INNER JOIN
          dbo.Product ON dbo.OrderItem.ProductId = dbo.Product.Id
WHERE    (dbo.[Order].PaidDateUtc BETWEEN CONVERT(DATETIME, '2014-01-06 00:00:00', 102) AND CONVERT(DATETIME, '2014-07-25 00:00:00', 102))
GROUP BY  dbo.OrderItem.ProductId, dbo.Product.SKU, dbo.Product.Name

Thanks,
Steve

Outer Join...

FROM      dbo.Product OUTER JOIN
          dbo.OrderItem ON dbo.Product.Id = dbo.OrderItem.ProductId INNER JOIN
          dbo.[Order] ON dbo.OrderItem.OrderId = dbo.[Order].Id
          

(and  SELECT    dbo.Product.Id ... )

OK, thanks Dennis.
However...I guess I need more than just to be pointed in the right direction...
I tried nesting the inner join inside of the outer join, like this:

SELECT    dbo.OrderItem.ProductId, 
          dbo.Product.Id,
          dbo.Product.SKU, 
          dbo.Product.Name, 
          SUM(dbo.OrderItem.Quantity) AS [# Units Sold], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax) AS [Total Sales Revenue], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Product Cost], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax - dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Profit]

FROM      dbo.Product OUTER JOIN
    dbo.OrderItem ON dbo.Product.Id = dbo.OrderItem.ProductId INNER JOIN
    dbo.[Order] ON dbo.OrderItem.OrderId = dbo.[Order].Id
WHERE    (dbo.[Order].PaidDateUtc BETWEEN CONVERT(DATETIME, '2014-01-06 00:00:00', 102) AND CONVERT(DATETIME, '2014-07-25 00:00:00', 102))
GROUP BY  dbo.OrderItem.ProductId, dbo.Product.SKU, dbo.Product.Name

...and it has syntax error at the outer 'join'

What did I do wrong?

Steve

I have tried a hundred different variations of this query and still not getting anywhere with it.
Can somebody please try to spot my mistake?

Thanks,
Steve

sorry, typo...  need "LEFT..."  - ie.LEFT OUTER JOIN  or LEFT JOIN

SELECT    dbo.OrderItem.ProductId, 
          dbo.Product.SKU, 
          dbo.Product.Name, 
          SUM(dbo.OrderItem.Quantity) AS [# Units Sold], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax) AS [Total Sales Revenue], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Product Cost], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax - dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Profit]

FROM  dbo.Product
LEFT OUTER JOIN dbo.OrderItem ON dbo.Product.Id = dbo.OrderItem.ProductId
INNER JOIN dbo.[Order] ON dbo.OrderItem.OrderId = dbo.[Order].Id
WHERE    (dbo.[Order].PaidDateUtc BETWEEN CONVERT(DATETIME, '2014-01-06 00:00:00', 102) AND CONVERT(DATETIME, '2014-07-25 00:00:00', 102))
GROUP BY  dbo.OrderItem.ProductId, dbo.Product.SKU, dbo.Product.Name

(Also, I removed redundant ' dbo.Product.Id,' in Select)

Thanks, but unfortunately, this gives exactly the same results as my initially posted query...it only shows records in which at least one product sold..

It doesn't include records in which none sold during the given date range, which is what I need.

Steve

OK, this time I actually tested it :)

SELECT    dbo.OrderItem.ProductId, 
          dbo.Product.SKU, 
          dbo.Product.Name, 
          SUM(dbo.OrderItem.Quantity) AS [# Units Sold], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax) AS [Total Sales Revenue], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Product Cost], 
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax - dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Profit]

FROM  dbo.Product
LEFT JOIN dbo.OrderItem
       ON dbo.Product.Id = dbo.OrderItem.ProductId
LEFT JOIN dbo.[Order]
       ON dbo.OrderItem.OrderId = dbo.[Order].Id
      AND (dbo.[Order].PaidDateUtc BETWEEN CONVERT(DATETIME, '2014-01-06 00:00:00', 102) AND CONVERT(DATETIME, '2014-07-25 00:00:00', 102))
GROUP BY  dbo.OrderItem.ProductId, dbo.Product.SKU, dbo.Product.Name
ORDER BY  dbo.OrderItem.ProductId, dbo.Product.SKU, dbo.Product.Name

ahh....two left joins.....

You are the man, Dennis!

Many thanks-
Steve

Well...it's me again....
Turns out that the query isn't right after all....it is now showing products that did not sell, but the counts and sums of sold items is waaaaay off...by hundreds or thousands in some cases.

Steve

Note that the query does not exclude cancelled orders, etc.

Run the query without any date filter (i.e. remove AND (dbo.[Order].PaidDateUtc BETWEEN ...)
Then, get a few ProductId's your concerned with, and compare the results with this very simple query that only uses the OrderItem table

SELECT    dbo.OrderItem.ProductId,
          SUM(dbo.OrderItem.Quantity) AS [# Units Sold],
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax) AS [Total Sales Revenue],
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Product Cost],
          SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax - dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Profit]
FROM dbo.OrderItem
WHERE ProductId in (5,7,31,51)
GROUP BY  dbo.OrderItem.ProductId
ORDER BY  dbo.OrderItem.ProductId

(replace the product ids)