Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Where are my zero Counts?! 1

Status
Not open for further replies.

j0em0mma

Programmer
Jul 31, 2003
131
US
I have done this a bil... let's just say many times. Given the following SQL:

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?
 
Left and inner join are cascaded... and then WHERE clause is on outermost table [banghead].

Make tbl_orders base table, then join from there.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Don't smack that head too hard, it'll stick that way :)

"Make tbl_orders base table, then join from there"

Isn't that what the second code sample from my original post is doing?
 
Let's go derived tables 101 [pipe]

This query:
Code:
select OI.ItemID, count(distinct O.OrderID) as orderCount
from tbl_Orders O
inner join tbl_OrderItems OI on O.OrderID = OI.OrderID
where O.DateEntered between '2005-03-13 14:28:15.530' and '2006-03-31 14:28:15.530' 
group by OI.ItemID
... returns ItemID and count of orders where they appear in specified datetime interval.

This query:
Code:
select I.ItemName as [Item Name], I.ItemDescription as [Item Description],
	isnull(X.orderCount, 0) as [Orders Distributed]
from dbo.tbl_Items
left outer join
(	<query from above>
) X
on X.ItemID = I.ItemID
where I.Active = 1
Simple, heh?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Well, I'll be dipped in something or other :).

I could swear that I'd gotten this to work with out the join to an inner select before. In my original question, the sample code with the Right Outer JOIN works until you apply the where clause. After looking at your solution, it makes sense why that failed. The filter needs to be applied before all the tables are smashed together, otherwise it removes the whole joined row...

Thanks, Kudos!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top