|
Subject:
|
Need help with creating a Parent Category
|
|
Posted By:
|
seanmayhew
|
Post Date:
|
11/10/2004 12:53:26 PM
|
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
|
|
Reply By:
|
englere
|
Reply Date:
|
11/10/2004 9:30:43 PM
|
This looks inefficient. Why not try a subquery:
SELECT NC1.CatID, NC1.Name
FROM News_Categories NC1
WHERE NC1.CatID =
(select NC2.ParentCatID
FROM News_Categories NC2
WHERE NC2.CatID = @CatID)
I suspect your real problem is with the data.
Eric
|
|
Reply By:
|
seanmayhew
|
Reply Date:
|
11/13/2004 5:20:54 PM
|
Actually this worked and it turned out my original was working but Im trying to give the user the friendly name of the category and not the category id. So for some reason it will display the correct parent category id but there is some sort of skip thats returning the grandparent name if I omit the function to get the friendly name i have the right ID displayed go figure.
|
