October 20, 2008 at 5:41 am
Hi all, i am building a category tree like structure for my FAQ app. i need to show all active categories. The hitch is that if a parent category is checked as inactive, then the sub or child categories under it should not be displayed even if they are marked as active. I need a query which will return me such a resultset which i can display in a table. the columns in my table are categoryid, category, ParentcategoryID,companyID and active.
the ParentcategoryID contains id of another category or 'No parentcategory' which denotes its the main category. It is easy to get the immediate parentcategory and find out if its active or not. But to find out if that parent category's parent and so on is active or not is tough. please help.
October 24, 2008 at 6:53 am
The following represents an example of a self reference table hierarchy example. You can see that the sub items of the item marked as inactive are not displayed.
CREATE TABLE #SELFREF (id int IDENTITY NOT NULL, parentId int, description varchar(50), active bit)
INSERT INTO #SELFREF
(
parentId,
description,
active
)
SELECT NULL, 'Root Item',1
UNION
SELECT 1,'Tree Item 1', 1
UNION
SELECT 1,'Tree Item 2', 1
UNION
SELECT 2, 'Tree Item 1 SubItem 1',1
UNION
SELECT 2, 'Tree Item 1 SubItem 2', 1
UNION
SELECT 3, 'Tree Item 2 SubItem 1', 0
UNION
SELECT 3, 'Tree Item 2 SubItem 2', 1
UNION
SELECT 4, 'Tree Item 1 SubItem 1 SubItem 1', 1
UNION
SELECT 6, 'Tree Item 2 SubItem 1 SubItem 1', 1
UNION
SELECT 9, 'Tree Item 2 SubItem 1 SubItem 1 SubItem 1', 1
CREATE TABLE #Results (id int, parentId int, description varchar(50), hierarchyLevel int, active bit)
DECLARE
@done bit,
@hierarchyLevel int
SET @done = 0
SET @hierarchyLevel = 0
WHILE @done = 0
BEGIN
IF @hierarchyLevel = 0
BEGIN
INSERT INTO #RESULTS (id, parentId, description, hierarchyLevel, active)
SELECT id, parentId, description, @hierarchyLevel, active FROM #SELFREF WHERE parentId IS NULL
END
ELSE
BEGIN
Print 'Root Item Insert'
INSERT INTO #RESULTS
(
id,
parentId,
description,
hierarchyLevel,
active
)
SELECT
SR.id,
SR.parentId,
SR.description,
@hierarchyLevel,
SR.active
FROM
#RESULTS R LEFT JOIN #selfref SR ON R.ID = SR.parentId
LEFT JOIN #RESULTS R2 ON SR.id = R2.ID
WHERE
R.active = 1
AND R2.id IS NULL-- Prevents recursion
AND SR.id IS NOT NULL
IF @@ROWCOUNT = 0
SET @done = 1
END
SET @hierarchyLevel = @hierarchyLevel + 1
END
SELECT * FROM #selfref
SELECT * FROM #results
DROP TABLE #selfref
DROP TABLE #Results
October 27, 2008 at 12:39 am
Hi carnaud,
Thanks for your answer, but this doesnt really solve my problem. As we have a separate page for selecting a particular category in the tree view, the user is taken to a page that displays only that node. So when he marks it as inactive. i have only that node( categoryid) and i take that node to the database to mark as inactive. Now all the subcategroies under that category upto the last one in the tree node are to be marked as inactive.
So i have to loop thru all the records in teh table using the selected category id as parent category id and mark those as inactive. Then i have to take all the ids of the categories that i had marked as inactive and search in the table for those records whose parent categoryids are the marked categories and mark those also as inactive and so on until i have made that part of the tree structure as inactive.
October 27, 2008 at 7:15 am
I am not sure if I understand completely. I imagine that the categories you speak of are treated just like tree nodes, and that a node can NOT have more than one parent. If that is the case, then it is should be a simple matter walking the hierarchy and caching both the parent and the child nodes of the parent in a table to be used in a join to update the other records related to the nodes that have been deactivated. If this is the case, then the example I provided can be altered to be implemented in that fashion.
I am sure I could provide a solution if you provide a database schema and a clearer example of the functionality you are trying to engineer.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply