SQL Query to return Zero count Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query to return Zero count Records

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2006-03-14 : 18:35:04

Hello:

I'm new to the site. I'm trying to get this query working so zero count records display. The first query works fine when I don't have a WHERE statement. As soon as I add the WHERE statement, records with zero results are purged. I would like all product records to display regardless of what is specified in the where statements.

Query #1:

SELECT prod.ProductName, prod.ProductID, COUNT(detail.hotfixID) ProductCount
FROM SWCentral.dbo.DETAIL detail RIGHT OUTER JOIN SWCentral.dbo.PRODUCT prod ON (detail.product = prod.ProductID) LEFT OUTER JOIN SWCentral.dbo.RELEASE_HOTFIX release ON (detail.hotfixID = release.HotFixID) LEFT OUTER JOIN SWCentral.dbo.STATUS status ON (release.ReleaseStatus = status.StatusID)

GROUP BY prod.ProductName, prod.ProductID

Product ID Product Name ProductCount
1 Product A 95
2 Product B 6
3 Product C 0
4 Product D 21
5 Product E 10
6 Product F 5
7 Product G 18
8 Product H 6
9 Product I 7

Query #2:

SELECT prod.ProductName, prod.ProductID, COUNT(detail.hotfixID) ProductCount
FROM SWCentral.dbo.DETAIL detail RIGHT OUTER JOIN SWCentral.dbo.PRODUCT prod ON (detail.product = prod.ProductID) LEFT OUTER JOIN SWCentral.dbo.RELEASE_HOTFIX release ON (detail.hotfixID = release.HotFixID) LEFT OUTER JOIN SWCentral.dbo.STATUS status ON (release.ReleaseStatus = status.StatusID)

WHERE ((release.PrimaryRelease = 1) AND (status.StatusDesc <> 'Closed'))
GROUP BY prod.ProductName, prod.ProductID

Product ID Product Name ProductCount
1 Product A 95
2 Product B 6
4 Product D 21
5 Product E 10
6 Product F 5
7 Product G 18
8 Product H 6

I would like Product C and Product I to display with a ProductCount of 0.

Any suggestions on what I am doing wrong are appreciated.

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-03-15 : 07:41:50

Basically when you add a WHERE clause using OUTER JOIN like you have done you turn them into INNER JOINS.

Here is a one query that works, sorry I changed the table names while I was testing, but you get the idea:


SELECT 
	prod.ProductName, 
	prod.ProductID, 
	COUNT(t.hotfixID) ProductCount 
FROM 
	PRODUCT prod 
LEFT OUTER JOIN
(
	SELECT  
		detail.productid, detail.hotfixID
	FROM
		DETAIL detail 
	INNER JOIN
		Release release 
		ON (detail.hotfixID = release.HotFixID) 
	INNER JOIN 
		STATUS status 
		ON (release.ReleaseStatus = status.StatusID)
	WHERE 
		release.PrimaryRelease = 1 
		AND status.StatusDesc <> 'Closed' 
) t
ON 
	(t.productid = prod.ProductID) 
GROUP BY 
	prod.ProductName, 
	prod.ProductID

-Lamprey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources