I have done this a bil... let's just say many times. Given the following SQL:
An Item that doesn't appear on the Inner Joined tbl_OrderItems and Orders Tables should be displayed with a zero in the "Orders Distributed" aggregate column. I recall in the past being able to reverse the order of the FROM clause and use a RIGHT OUTER JOIN to the Items table and sometimes get success.
I have tried this time, but no luck. What am I doing wrong?
Code:
SELECT
dbo.tbl_Items.ItemName AS [Item Name],
dbo.tbl_Items.ItemDescription AS [Item Description],
COUNT(Distinct(dbo.tbl_Orders.OrderID)) AS [Orders Distributed]
FROM
dbo.tbl_Items LEFT OUTER JOIN dbo.tbl_OrderItems ON dbo.tbl_Items.ItemID = dbo.tbl_OrderItems.ItemID
INNER JOIN dbo.tbl_Orders ON dbo.tbl_OrderItems.OrderID = dbo.tbl_Orders.OrderID
WHERE dbo.tbl_Orders.DateEntered >= '2005-03-13 14:28:15.530' AND dbo.tbl_Orders.DateEntered <= '2006-03-31 14:28:15.530' AND dbo.tbl_Items.Active = 1
GROUP BY dbo.tbl_Items.ItemName, dbo.tbl_Items.ItemDescription
ORDER BY dbo.tbl_Items.ItemName
An Item that doesn't appear on the Inner Joined tbl_OrderItems and Orders Tables should be displayed with a zero in the "Orders Distributed" aggregate column. I recall in the past being able to reverse the order of the FROM clause and use a RIGHT OUTER JOIN to the Items table and sometimes get success.
Code:
SELECT
dbo.tbl_Items.ItemName AS [Item Name],
dbo.tbl_Items.ItemDescription AS [Item Description],
COUNT(Distinct(dbo.tbl_Orders.OrderID)) AS [Orders Distributed]
FROM
dbo.tbl_Orders INNER JOIN dbo.tbl_OrderItems on dbo.tbl_Orders.OrderID = dbo.tbl_OrderItems.OrderID
RIGHT OUTER JOIN dbo.tbl_Items ON dbo.tbl_OrderItems.ItemID = dbo.tbl_Items.ItemID
WHERE dbo.tbl_Orders.DateEntered >= '2005-03-13 14:28:15.530' AND dbo.tbl_Orders.DateEntered <= '2006-03-31 14:28:15.530' AND dbo.tbl_Items.Active = 1
GROUP BY dbo.tbl_Items.ItemName, dbo.tbl_Items.ItemDescription
ORDER BY dbo.tbl_Items.ItemName
I have tried this time, but no luck. What am I doing wrong?