Select Records where count Not Zero

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)

Select Records where count Not Zero

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-03-01 : 08:59:11

Hi There,
I create a record set using:
strQuery = "select * from tbMatTypes"
oRs = New DbConn(strQuery)
Here I iterate through the recordset and create a second record set to get the details based on the first recordset.
While oRs.ReadData.Read()
strQuery = "select Count(Typeid) as TotOutput from tbMatTrack where TypeId=" & oRs.ReadData("TypeId").ToString()

My problem is that this returns records with a count of zero, I only want records that have a count. How could I change my select statement in the second recordset to have only positive count values.

Thanks for your help.

Best regards,

Steve

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-01 : 11:46:08

Do it all in one step and keep code set-based, not procedural.

SELECT		a.ID,
		COUNT(*) AS Items
FROM		tbMatTypes AS a
INNER JOIN	tbMatTrack AS b ON b.TypeID = a.TypeID
GROUP BY	a.ID

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources