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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
