View Single Post
  #1 (permalink)  
Old November 10th, 2004, 01:53 PM
seanmayhew seanmayhew is offline
Authorized User
 
Join Date: Nov 2003
Location: , , .
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to seanmayhew
Default Need help with creating a Parent Category

I've added a new column called "ParentID" to the News_Categories table that holds unsurpsingly the "CategoryID" of the categories parent. This works fine when its one parent deep. So... Say I have 3 categories Cat1 - Cat2 - Cat3 if the ParentID for Cat3 is Cat1 it returns Cat1. This is okay. But if I make a Cat4 with a ParentID of Cat3 it returns Cat1 which would actually be the Grandparent! This is as deep as it goes If I add a Cat5 with ParentID of Cat4 it returns the Cat3 ID... the grandparent.

Here is my stored procedure

CREATE PROCEDURE sp_News_ParentCat
@CatID int
AS

DECLARE @HoldParentID int

SELECT @HoldParentID = ParentCatID
    FROM News_Categories
    WHERE CatID = @CatID

SELECT CatID, Name
    FROM News_Categories
    WHERE CatID = @HoldParentID
GO