p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: ASP.NET Website Programming Problem-Design-Solution (http://p2p.wrox.com/forumdisplay.php?f=23)
-   -   Need help with creating a Parent Category (http://p2p.wrox.com/showthread.php?t=21071)

seanmayhew November 10th, 2004 01:53 PM

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

englere November 10th, 2004 10:30 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

seanmayhew November 13th, 2004 06:20 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.



All times are GMT -4. The time now is 08:25 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.