Parent Child transversing

I have a parent/child relationship in a relational database broken out like this:

Table Name: categories
[category_id] int (primary_key NOT NULL),
[category_name] varchar(50),
[parent_fk] int

The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this:

select category_id, category, parent_fk
from categories
where
category_id not in (
select parent_fk from categories
)

Each bottom-level category has a count attached to it.

The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts).

My sql is a little weak, could you help me out? I can utilize everying in SQL 2000 (stored proc, UDF, anything).

Thanks!

Attached is a diagram that might help:
http://www.cnsar.com/images/diagram.gif
|||

This procedure (below) is based on the following table layout:

CREATE TABLE categories (
category_id int,
desc_name varchar(35),
parent_fk int
)
-----------------------
CREATE PROCEDURE GetChildren
@.category_idint --pass in the parent.id in which you want to get the children
AS

SET NOCOUNT ON
CREATE TABLE #temp_children1(category_idintNOT NULL)
CREATE TABLE #temp_children2(category_idintNOT NULL)
CREATE TABLE #temp_allchildren(category_idintNOT NULL)

INSERT INTO #temp_children1VALUES(@.category_id)-- Parent
WHILEEXISTS(SELECT *FROM #temp_children1)
BEGIN
DELETE FROM #temp_children2
-- Save current level children
INSERT INTO #temp_children2
SELECT c.category_id
FROM categories cJOIN #temp_children1 t
ON c.parent_fk = t.category_id
WHERE c.parent_fk <> c.category_id

DELETE FROM #temp_children1
INSERT INTO #temp_children1
SELECT *FROM #temp_children2
-- Add to the list of all children

INSERT INTO #temp_allchildren
SELECT *FROM #temp_children2

END

--return the children ids
select category_id
from #temp_allchildren

--cleanup
DROP TABLE #temp_children1
DROP TABLE #temp_children2
DROP TABLE #temp_allchildren

GO

|||

Based on your diagram, I made the assumption that the count you want is the number of images that are associated with each category. If you just wanted the number of categories that are subchildren, you'll need to change the function a little bit, returning 1 in the first part of the if and returning SUM(dbo.ChildrenImageCount(CategoryID))+1 in the second part of the if in the ChildrenImageCount function (The commented out lines). It will probably be one more than you are expecting because it counts itself as well, but a small price to pay for ease of use. If you want the count of end-nodes, remove the +1.

Just an FYI -- Your root node should have a ParentFK of NULL, otherwise it'd be it's own parent. If you must continue to use 1, don't use the Foreign key constraint (It won't work -- I don't think), and don't ever select the count for the root node (It will loop forever until it blows up) -- unless of course you change it so it won't do that ;-)

GO
/****** Object: Table [dbo].[Categories] Script Date: 01/20/2006 01:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATETABLE [dbo].[Categories](

[CategoryID] [int]IDENTITY(1,1)NOTNULL,

[ParentFK] [int]NULL,

[Name] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_ASNOTNULL,

[ImageCount]AS([dbo].[ChildrenImageCount]([CategoryID])),

CONSTRAINT [PK_Categories]PRIMARYKEYCLUSTERED

(

[CategoryID]ASC

)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]

)ON [PRIMARY]

GO

SETANSI_PADDINGOFF

GO

ALTERTABLE [dbo].[Categories]WITHCHECKADDCONSTRAINT [FK_Categories_Categories]FOREIGNKEY([ParentFK])

REFERENCES [dbo].[Categories]([CategoryID])

GO
/****** Object: Table [dbo].[Images] Script Date: 01/20/2006 01:42:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Images](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NOT NULL,
[Image] [image] NULL,
[ImageMime] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

GO
/****** Object: UserDefinedFunction [dbo].[ChildrenImageCount] Script Date: 01/20/2006 01:42:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ChildrenImageCount]
(
-- Add the parameters for the function here
@.CategoryID int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @.Result int

IF NOT EXISTS(SELECT CategoryID FROM Categories WHEREParentFK=@.CategoryID)
BEGIN
SELECT @.Result=COUNT(*) FROM Images WHERECategoryID=@.CategoryID

-- SELECT @.Result=1

END
ELSE
BEGIN
-- Add the T-SQL statements to compute the return value here
SELECT @.Result=SUM(dbo.ChildrenImageCount(CategoryID)) FROM Categories WHEREParentFK=@.CategoryID

-- SELECT @.Result=SUM(dbo.ChildrenImageCount(CategoryID))+1 FROM Categories WHEREParentFK=@.CategoryID

END

-- Return the result of the function
RETURN @.Result

END

Once you make those changes, you can just select ImageCount from the categories table for any record and it will have the right answer for you.