July 29, 2007 at 1:42 pm
Please post expected output, based on previous posted sample data.
N 56°04'39.16"
E 12°55'05.25"
July 30, 2007 at 12:17 am
Hi Peter,
I want to retrieve all the children and childrens children... of a given category including parent. assume that if i want to find category 10's sub and sub sub categories it should return all sub and sub sub categories of 10 which are 11 to 23 and its parent 10, for 14 children are 15 to 19 and 14......however the sub categories may not be in serial manner. 1's childrens are 2,3,4 and parent 1, 5's children parent 5 and 6,7,8,9. assume the phenomenon a given parent does not have any children it should return the parent itself. like to find childrens of a parent 2 it returns 2 itself, for 6 it returns 6.
For more clear picture i am gonna use this algorithm for searching a product in a given category. hence if a customer is searching for a product in a selected cateogy it should return all the sub categories of the given category. if the given category had no children it returns the category itself.
July 30, 2007 at 12:51 am
Please, please, please try the algorithm I posted earlier.
It will give you the "starting" category and all children.
N 56°04'39.16"
E 12°55'05.25"
July 30, 2007 at 5:43 am
I have tried the function you posted. The UDF you posted in this question and my previous question are working in the opposite way of my expectation that too with the table in which the tuples are inserted by union all as you specified in your previous answer.
Your functions are not working with natural tables in which the tuples are not inserted by union all. and not producing expected output.
when i pass argument categoryid 10 to the DBO.FNGETHIERARCHY(10), it returns the following table
select * from DBO.FNGetHierarchy(10)
Catid CatName Depth
10 Laptop Spare Parts 0
0 Laptop Spare Parts 1
The output i am expecting
select * from dbo.fngethierarchy(10)
Catid CategoryName Depth
10 Laptop Spare Parts 0
11 Hard Drives 1
12 1.8 2
13 2.5 2
14 Memory RAM 1
15 PC100 2
16 PC133 2
17 DDR 2
18 DDR2 2
19 Other Specific 2
20 AC Adapters 1
21 Toshiba 2
22 Samsung 2
23 ACER 2
So please kindly provide a correct solution. and i need to know how can i make union all my existing tuples in the table.
July 30, 2007 at 5:32 pm
Vamshi,
Did you ever resolve your problem? If not, you might want to check out "Expanding Hierarchies" in Books Online.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2007 at 1:18 am
Peter,
Select currentcategory.CategoryID, currentcategory.CategoryName, childitem.CategoryID, childitem.CategoryName
from Categories currentcategory inner join Categories childitem
on currentcategory.CategoryID = childitem.ParentcategoryID
with the above query i can retrive all parents, children and childrens children... how can i retrive particular parent and its children and childrens children for a given cateogry only?
July 31, 2007 at 10:01 pm
Alright... first, I hate "Adjacency Models"... they're relatively slow and I hate recalculating things every time I want to look something up. Second, product tables don't change that often so you shouldn't have to recalculate all the time.
Joe Celko has written many articles and a book on using a "Nested Set" model. I've modified his code for this example... let's start out with some test data so we can all get on the same page... note the this data is not quite the same as Peter's test data which had a "product loop" or "self reference". Not that it's wrong... it's just that this one doesn't and won't handle it if you do... it must be a clean product hierarchy and you must have a "zero row" with a NULL parent at the top of the structure.
DROP TABLE [Sample]
-- Create Sample data
CREATE TABLE [Sample]
(
CatID INT,
ParentID INT,
CategoryName VARCHAR(30),
PRIMARY KEY (CatID)
)
INSERT [Sample]
(CatID,ParentID,CategoryName)
SELECT 0,NULL,'All Products' UNION ALL
SELECT 1,0,'Laptops' UNION ALL
SELECT 2,1,'Brand New' UNION ALL
SELECT 3,1,'Refurbished' UNION ALL
SELECT 4,1,'Secondhand' UNION ALL
SELECT 5,0,'Desktops' UNION ALL
SELECT 6,5,'Brand New' UNION ALL
SELECT 7,5,'Refurbished' UNION ALL
SELECT 8,5,'Secondhand' UNION ALL
SELECT 9,5,'Custom Built' UNION ALL
SELECT 10,0,'Laptop Spare Parts' UNION ALL
SELECT 11,10,'Hard Drives' UNION ALL
SELECT 12,11,'1.8' UNION ALL
SELECT 13,11,'2.5' UNION ALL
SELECT 14,10,'Memory RAM' UNION ALL
SELECT 15,14,'PC100' UNION ALL
SELECT 16,14,'PC133' UNION ALL
SELECT 17,14,'DDR' UNION ALL
SELECT 18,14,'DDR2' UNION ALL
SELECT 19,14,'Other Specific' UNION ALL
SELECT 20,10,'AC Adapters' UNION ALL
SELECT 21,20,'Toshiba' UNION ALL
SELECT 22,20,'Samsung'
Now... run the following...
--===== Suppress auto-display of line counts for appearance and speed
SET NOCOUNT ON
--===== If tempory tables for this test exist, drop them
IF OBJECT_ID('TempDB..#ScratchPad') IS NOT NULL
DROP TABLE #ScratchPad
IF OBJECT_ID('TempDB..#Tree') IS NOT NULL
DROP TABLE #Tree
--===== Create the #ScratchPad table to temporarily hold data whil the process runs
CREATE TABLE #ScratchPad
(
CatID INT NOT NULL,
ParentID INT
)
--===== Create the #Tree Table which will start empty
CREATE TABLE #Tree
(
Level INT NOT NULL,
CatID INT NOT NULL,
ParentID INT,
LeftBower INT,
RightBower INT
)
--===== Declare Local Variables
DECLARE @MyCounter INT
DECLARE @MaxCount INT
DECLARE @Level INT
--===== Populate the #ScratchPad table from the data table
INSERT INTO #ScratchPad
(CatID, ParentID)
SELECT CatID, ParentID
FROM [Sample]
--======================================================================================
-- Build the tree
--======================================================================================
--===== Presets
SET @MyCounter = 2
SET @MaxCount = 2 * (SELECT COUNT(*) FROM #ScratchPad)
SET @Level = 1
--===== Put the number one dog into the tree
INSERT INTO #Tree
(Level, CatID,ParentID,LeftBower,RightBower)
SELECT 1 AS Level,
CatID AS CatID,
ParentID,
1 AS LeftBower,
NULL AS RightBower --Will be determined later
FROM #ScratchPad
WHERE ParentID IS NULL
--===== Build the tree
WHILE @MyCounter <= (@MaxCount)
BEGIN
IF EXISTS ( -- See if anything left to do at this level
SELECT *
FROM #Tree AS t,
#ScratchPad AS s
WHERE t.CatID = s.ParentID
AND t.Level = @Level
)
BEGIN --===== Push when Level has subordinates, set LeftBower value
INSERT INTO #Tree
(Level,CatID,ParentID,LeftBower,RightBower)
SELECT (@Level + 1) AS Level,
MIN(s.CatID) AS CatID,
MIN(s.ParentID) AS ParentID,
@MyCounter AS LeftBower,
NULL AS RightBower --Will be determined on the way back up
FROM #Tree AS t,
#ScratchPad AS s
WHERE t.CatID = s.ParentID
AND t.Level = @Level
--===== Delete each item inserted in #Tree
DELETE FROM #ScratchPad
WHERE CatID = (SELECT CatID
FROM #Tree
WHERE Level = @Level + 1)
--===== Update the counters for the next item down
SET @MyCounter = @MyCounter + 1
SET @Level = @Level + 1
END
ELSE
BEGIN --===== Pop the #Tree and set RightBower value
UPDATE #Tree
SET RightBower = @MyCounter,
Level = -Level -- pops the #Tree
WHERE Level = @Level
--===== Update the counters for the next item up
SET @MyCounter = @MyCounter + 1
SET @Level = @Level - 1
END
END --WHILE
--===== Update the #Tree table Level for positive numbers
-- If any negatives continue to exist, then big problem
UPDATE #Tree
SET Level = -Level
--===== Display the results
PRINT '===== #Tree table ====='
SELECT * FROM #Tree
What that does is it builds a table called "Tree". I used a temp table just to make sure it doesn't blow anything of yours out of the water. You should convert it to a permanent table.
Now, run these... they're all examples of how to find things both in the upline and in the downline for any given CatID... as with most any of my code, read the comments and the constants to see what's going on...
--===== Return the downline including the desired row.
SELECT t.Level-b.Level AS RelativeLevel,t.CatID,t.ParentID,t.LeftBower,t.RightBower
FROM #Tree t,
(--==== Derived table finds the bowers for a given CatID
SELECT Level,LeftBower,RightBower
FROM #Tree
WHERE CatID = 10
) b
WHERE t.LeftBower >= b.LeftBower
AND t.RightBower <= b.RightBower
ORDER BY t.LeftBower
--===== Return the downline excluding the desired row.
SELECT t.Level-b.Level AS RelativeLevel,t.CatID,t.ParentID,t.LeftBower,t.RightBower
FROM #Tree t,
(--==== Derived table finds the bowers for a given CatID
SELECT Level,LeftBower,RightBower
FROM #Tree
WHERE CatID = 10
) b
WHERE t.LeftBower > b.LeftBower
AND t.RightBower < b.RightBower
ORDER BY t.LeftBower
--===== Return the upline including the desired row.
SELECT t.Level-b.Level AS RelativeLevel,t.CatID,t.ParentID,t.LeftBower,t.RightBower
FROM #Tree t,
(--==== Derived table finds the bowers for a given CatID
SELECT Level,LeftBower,RightBower
FROM #Tree
WHERE CatID = 22
) b
WHERE t.LeftBower <= b.LeftBower
AND t.RightBower >= b.RightBower
ORDER BY t.LeftBower
--===== Return the upline excluding the desired row.
SELECT t.Level-b.Level AS RelativeLevel,t.CatID,t.ParentID,t.LeftBower,t.RightBower
FROM #Tree t,
(--==== Derived table finds the bowers for a given CatID
SELECT Level,LeftBower,RightBower
FROM #Tree
WHERE CatID = 22
) b
WHERE t.LeftBower < b.LeftBower
AND t.RightBower > b.RightBower
ORDER BY t.LeftBower
A "Bower" is defined as one of the two anchors on the front of large ships... one on the left side, and one on the right (Port and Starboard, actually).
For a full explanation of how these "Nested Set Models" work, please see the following URL...
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=1266295
... Joe recommends fully replacing the Adjacency Model with the Nested Set Model. That's up to you or not. The folks at work don't really care for Nested Set Models (actually, they just don't understand them) so I maintain both. Also, Joes code has a bug or two in it... be careful... make sure you test everything if you decide to go with his examples.
Why does my selection code differ so much from Joe's? Well, other than the fact that my code contains Level and an idication of Level Direction, it's just a bit easier to see what's going on... his code and mine create identical execution plans.
Either way, the Nested Set Model allows for the fastest hierarchical lookups I've ever seen (once the model or tree has been created). They are trully set based lookups that will benefit nicely in the presence of the correct indexes. It also forces you to have correct data or the model blows up (all negative values for LEVEL in the tree if just about anything goes wrong).
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2007 at 11:34 pm
August 1, 2007 at 6:45 pm
Use your sample table... I just created my own and posted so other folks could see what's going on.
If that's not what you meant, you need to explain to me what you mean by doing a "union all now"... again, I was just making test data... you need to modify the code I posted to fit your table and column names.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply