?
Solved

Category Tree Stored Procedure

Posted on 2005-04-26
7
Medium Priority
?
520 Views
Last Modified: 2012-08-14
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!
0
Comment
Question by:mmarksbury
  • 4
  • 3
7 Comments
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 2000 total points
ID: 13872997
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
 
LVL 7

Author Comment

by:mmarksbury
ID: 13873078
Error: 8154: The table '@CategoryTree' is ambiguous

Looks to be coming from WHILE Loop ...
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13875971
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 7

Author Comment

by:mmarksbury
ID: 13881737
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
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13882092
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
 
LVL 7

Author Comment

by:mmarksbury
ID: 13882138
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
 
LVL 7

Author Comment

by:mmarksbury
ID: 13882201
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question