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