Category Tree Stored Procedure

This is driving me crazy, as SPs are not my specialty.  I know what needs to be done, but I seem to be getting lost in the middle.  Help is greatly appreciated.  500 Points for first correct answer.  Thanks in advance.

Tables used shown below.  Only needed columns displayed . . .

Category
---------------
CategoryId   int -- PK
Title   varchar(25)

Category_Relationship
---------------------------------
RelationshipId   int -- PK
ParentCategoryId   int -- FK
ChildCategoryId   int -- FK

Item
---------
ItemId    int -- PK

Category_Assignment
--------------------------
AssignmentId   int -- PK
CategoryId   int -- FK
ItemId   int -- FK


A Few Facts to Note:
--------------------------
* Categories can be assigned to 'n' Categories.
* Items can be assigned to 'n' Categories

What I need:
----------------
Someone to finish the MS SQL Stored Procedure I have started that will create a temporary table and fill it with the category hierarchy in my DB.

What I have so Far:
------------------------------------------------------------
CREATE PROCEDURE dbo.GetCategoryTree
AS
DECLARE @CategoryTree TABLE(CategoryID INT, CategoryTitle VARCHAR(25), ParentCategoryID INT, Depth INT, ItemCount INT)

DECLARE @Depth INT
SET @Depth = 0

/* MISSING LOGIC */

SELECT * FROM @CategoryTree
GO

------------------------------------------------------------
As you can see, I am missing the core logic, which is why I am here, asking this question.  Thanks in advance!
LVL 7
mmarksburyAsked:
Who is Participating?
 
rafranciscoCommented:
Try changing this part:

WHILE EXISTS (SELECT 'X' FROM @CategoryTree WHERE Depth IS NULL)
BEGIN
    UPDATE @CategoryTree
    SET Depth = B.Depth + 1
    FROM @CategoryTree A INNER JOIN @CategoryTree B
                                ON A.ParentCategoryID = B.CategoryID
    WHERE A.Depth IS NULL AND B.Depth IS NOT NULL
END
0
 
rafranciscoCommented:
INSERT INTO @CategoryTree (CategoryID, CategoryTitle, ParentCategoryID)
SELECT A.CategoryID, A.Title, B.ParentCategoryID
FROM Category A LEFT OUTER JOIN Category_Relationship B
    ON A.CategoryID = B.ChildCategoryID

DECLARE @Depth INT
SET @Depth = 0

UPDATE @CategoryTree
SET Depth = @Depth
WHERE ParentCategoryID IS NULL

WHILE EXISTS (SELECT 'X' FROM @CategoryTree WHERE Depth IS NULL)
BEGIN
    UPDATE @CategoryTree
    SET Depth = B.Depth + 1
    FROM @CategoryTree A INNER JOIN @CategoryTree B
                                ON A.CategoryID = B.ParentCategoryID
    WHERE A.Depth IS NULL AND B.Depth IS NOT NULL
END

UPDATE A
SET ItemCount = B.ItemCount
FROM @CategoryTree A INNER JOIN (SELECT CategoryID, COUNT(*) AS ItemCount FROM Category_Assignment
                                                      GROUP BY CategoryID) B
    ON A.CategoryID = B.CategoryID
0
 
mmarksburyAuthor Commented:
Error: 8154: The table '@CategoryTree' is ambiguous

Looks to be coming from WHILE Loop ...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rafranciscoCommented:
Change the WHILE loop to this one.

WHILE EXISTS (SELECT 'X' FROM @CategoryTree WHERE Depth IS NULL)
BEGIN
    UPDATE A
    SET Depth = B.Depth + 1
    FROM @CategoryTree A INNER JOIN @CategoryTree B
                                ON A.CategoryID = B.ParentCategoryID
    WHERE A.Depth IS NULL AND B.Depth IS NOT NULL
END
0
 
mmarksburyAuthor Commented:
This now allows execution, but the procedure runs in a never ending loop.  I'll try modifying it to work, but any thoughts or corrections would be appreciated . . .
0
 
mmarksburyAuthor Commented:
Nope, we get the "The table '@CategoryTree' is ambiguous." error again.

When I change it to this . . .

WHILE EXISTS (SELECT 'X' FROM @CategoryTree WHERE Depth IS NULL)
BEGIN
    UPDATE A
    SET Depth = B.Depth + 1
    FROM @CategoryTree A INNER JOIN @CategoryTree B
                                ON A.ParentCategoryID = B.CategoryID
    WHERE A.Depth IS NULL AND B.Depth IS NOT NULL
END

It will execute, but a row that should have a depth of 3 is shown as 2.
0
 
mmarksburyAuthor Commented:
I take that back . . .

Once I changed "UPDATE @CategoryTree" to "UPDATE A" the query worked fine.

Though, I think I may have a flaw in my table design.  This is not looking like I will be able to have N categories deep with each category having N possible assignements to other categories.

You get the points, but any suggestions?

For example if I have the following structure

- Cat 1
   - SubCat1
   - SubCat2
      - SuBSubCat1
      - SubSubCat2
   -SubCat3

- Cat2

Now, say I want to Assign "SubCat2" to "Cat2".  Using the query you have put together, I will get SubCat2 as a child of Cat2, but I will not get the children of SubCat2.

What I want is, if I assigned SubCat2 to Cat2, I would get the following output . . .

- Cat 1
   - SubCat1
   - SubCat2
      - SuBSubCat1
      - SubSubCat2
   -SubCat3

- Cat2
 - SubCat2
      - SuBSubCat1
      - SubSubCat2

Any suggestions?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.