Completely off the top of my head... not tested and I am not a SQL guru...
Select count(a.category) as Qty, a.category, b.ID >From products a, categories b Where a.category = b.category Group by a.category, b.ID Order by a.category Basically you want to look at group function -----Original Message----- From: Dave Long [mailto:[email protected]] Sent: Tuesday, December 20, 2011 2:25 PM To: cf-talk Subject: How many products in a specific category? Sorry to bother you folks with what is likely to be a very primitive problem but I've poured over my copy of "Mastering ColdFusion 5" with no success. I have a list of categories which is queried from a table called Categories. I have another table called products and it shares a common field named Category with the Categories table. In the template that displays the list of categories, I would like to include a parenthesis after the category name that displays the number of products entered in the products table with that category name. Here are the queries I'm using that aren't working. I can get the list of categories but the product count stops after one category. <CFQUERY name="GetCategories" dataSource="XYZ"> SELECT Categories.ID, Categories.CATEGORY, Categories.RANK, Categories.CATEGORY AS ID_Field FROM Categories ORDER BY Categories.RANK </CFQUERY> <CFQUERY name="GetProducts" dataSource="XYZ"> SELECT Products.ID, Products.CATEGORY FROM Products WHERE Products.CATEGORY = "#GetCategories.Category#" </CFQUERY> <cfinclude template="Header.htm"> <table align="center" cellpadding="10" cellspacing="0" border="0"><tr> <td align="center"><h3>Browse by Category:</h3></td></tr> <tr> <ul> <td> <cfoutput query="GetCategories"> <li> <a href="productitems.cfm?RecordID=#ID_Field#">#Category#</a> (#GetProducts.RecordCount#)</li> <br> </cfoutput> </td></ul></tr> <br> </table> <cfinclude template="Footer.htm"> I'm using MySQL 3.51 and CF5.0 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:349223 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
