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