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:349228 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
