If you are using Oracle, there is the CONNECT BY clause that is designed for use with Hierarchical data. I'm not sure if there is corresponding functionality in your database, but this may give you a starting point...
Steve ------------------------------------- Steven Monaghan Oracle DBA / Cold Fusion Developer MSC Industrial Direct Co., Inc. http://www.mscdirect.com ------------------------------------- -----Original Message----- From: Jason Davis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 19, 2002 3:20 PM To: CF-Talk Subject: Meet my tables Meet my Products table: CREATE TABLE [Products] ( [Product_id] [int] NOT NULL , [Product_title] [varchar] (200) NULL , [Category_id] [int] NOT NULL , ) Each product has a category, meet my categories table: CREATE TABLE [Categories] ( [Category_id] [int] NOT NULL , [Category_parent] [int] NULL , [Category_title] [varchar] (200) NULL , [Category_count] [int] NULL DEFAULT (0) ) As you can see, each category has a Category_parent which symbolize the above category (categories are sorted hirarchly). Can I do some kind of a loop that will run for each Category_id, and store in Category_count the number of Products available in that category, and all of the hirearchy categories beneath? i.e - if I have the following categories: (1) Printers (2) Laser (category_parent=1) (3) Ink (category_parent=1) and the following products: HP LaserJet (category_id = 2) HP Deskjet (category_id = 3) Canon BJ200D (Category_id = 3) category count will be: (1) Printers => 3 (1+2) (2) Laser => 1 (3) Ink => 2 Help!? ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
