Thanks, Aaron. I handled that with a conditional:
<cfoutput query="GetCategories"> <li type="square"><a href="productitems.cfm?RecordID=#ID_Field#">#CATEGORY# <CFIF ProductCount GT 0>(#ProductCount#)</CFIF></a></li><br> </cfoutput> See it in action at http://www.ireallywantstuff.com/categories.cfm The site is far from complete but I'm after it. Happy Holidays! Dave -----Original Message----- From: Aaron Rouse [mailto:[email protected]] Sent: Wednesday, December 21, 2011 3:38 PM To: cf-talk Subject: Re: How many products in a specific category? BTW, I did a left outer join in my example for if you ever have categories with zero products. Forgot to clarify that point earlier. Glad to hear you have things working out for your needs. Aaron On Wed, Dec 21, 2011 at 1:34 PM, Dave Long <[email protected]> wrote: > > Eureka!! > > A few minor modifications and your code worked like a charm. Not only > did the code work but I learned more about "Aggregate Functions and > GROUP BY," (page 174 of "Mastering...") > > Thanks so much to Aaron, Leigh, Akos and Jacob for your assistance. > > Dave > > > You should be able to just do this in one query. Syntax for the query > might be dependent upon what database you are using though. But > something like this general syntax should work: > > SELECT C.ID, C.CATEGORY, C.RANK, COUNT(P.ID) CNT > FROM CATEGORIES C LEFT OUTER JOIN PRODUCTS P ON C.CATEGORY = P.CATEGORY > GROUP BY C.ID, C.CATEGORY, C.RANK > ORDER BY C.RANK > > Then you can loop over the results, display your category and rank > then within the parenthesis you output the CNT for the count of > products for that category. I'd personally avoid putting a query > within a loop, it might be okay for this but can be a very bad habit > to get into. > > Aaron > > On Wed, Dec 21, 2011 at 11:17 AM, Dave Long <[email protected]> > wrote: > > > > > Well, I think you've put me on the right track, so thank you very > > much. I'm going to read up on "CFLOOP" in the book. > > > > Thanks again. > > > > Dave Long > > NorthGoods Merchant Services > > http://www.northgoods.com > > > > > > Maybe not the most efficient way but you can loop through the > > GetCategories query and within each iteration of the loop get the > > associated product count using another query. Not tested but this > > should work: > > > > <cfloop query="GetCategories"> > > <cfquery name="GetProducts" datasource="XYZ"> > > SELECT Products.ID, Products.CATEGORY > > FROM products > > WHERE Products.CATEGORY = #GetCategories.Categories.ID# > > </cfquery> > > > > <cfoutput>GetCategories.CATEGORY ( #GetPorducts.RecordCount # > > )</cfoutput> </cfloop> > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349231 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
