Re: Meet my tables
Sharon, Do you have a limit to how many levels the category hierarchy can be? until parent=NULL Thanks! - Original Message - From: Sharon Diorio [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 11:48 PM Subject: Re: Meet my tables Sharon DiOrio - Original Message - From: Jason Davis [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 3:19 PM 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!? __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Meet my tables
you should look at Joe Celko's work on using set theory to manage heirarchies, built to handle this sort of stuff... http://www.intelligententerprise.com/001020/celko.shtml -Original Message- From: Jason Davis [mailto:[EMAIL PROTECTED]] Sent: 19 March 2002 20:20 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Meet my tables
I would recommend that you do not keep the Category_Count as a column in your table. However, you might consider creating a view which dynamically grabbed the Count() of products for each category. Example: CREATE VIEW Category_List AS SELECT Cat.Category_ID, Cat.Category_Name, Cat.Category_Parent, (SELECT COUNT(Prod.Product_ID) FROM Products Prod WHERE Prod.Category_ID = Cat.Category_ID) As Category_Count FROM Categories Cat Then, you might be able to use cfloop to iterate through result set to total the hierarchal data for each child. I don't have the time to hash this out, but I hope this helps you out ^_^ -- SCOTT VAN VLIET SENIOR ANALYST SBC SERVICES, INC Tel: 858.886.3878 Fax: 858.653.6763 Email: [EMAIL PROTECTED] -Original Message- From: Jason Davis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 19, 2002 12: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!? __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
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!? __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Meet my tables
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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Meet my tables
Do you have a limit to how many levels the category hierarchy can be? Sharon DiOrio - Original Message - From: Jason Davis [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 3:19 PM 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Meet my tables
I've revisited this problem a million times and there is no easy solution. Oracle's CONNECT BY is very nice, but if you aren't using Oracle you have few options. You can do it with a loop, but it's not pretty and it won't scale. +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis -Original Message- From: Sharon Diorio [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 19, 2002 1:49 PM To: CF-Talk Subject: Re: Meet my tables Do you have a limit to how many levels the category hierarchy can be? Sharon DiOrio - Original Message - From: Jason Davis [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, March 19, 2002 3:19 PM 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
