How to find depth of hierarchy level
-
consider the following scanario of categories table
CatID ParentID CategoryName
---------------------------------
1 0 Laptops
2 1 Brand New
3 1 Refurbished
4 1 Secondhand
5 0 Desktops
6 5 Brand New
7 5 Refurbished
8 5 Secondhand
9 5 Custom Built
10 0 Laptop Spare Parts
11 10 Hard Drives
12 11 1.8
13 11 2.5
14 10 Memory RAM
15 14 PC100
16 14 PC133
17 14 DDR
18 14 DDR2
19 14 Other Specific
20 10 AC Adapters
21 20 Toshiba
22 20 Samsung
23 20 ACER
level of category Laptops,Desktops,Laptop Spareparts is 0, level of Hard drives , Memory Ram is 1, Level of DDR, DDR2 and PC100 is 2 without using a additional column levelno how can i find the level of the category based on their categoryID??
-
One option is to add a column and store the depth there. When you have that, it is very easy to add a new child!
Just select the parent, get the depth, add 1 and store the child.
N 56°04'39.16"
E 12°55'05.25" -
Pretty sure the OP said something to the effect of "without using a additional column levelno".
Vamshi,
Lookup "Expanding Hierarchies" in Books Online...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, I know. But I have never understood the idea of calculating the depth every time over and over again...
N 56°04'39.16"
E 12°55'05.25" -
Someone is mentoring instead of providing requested solution.
Isn't it you Peter?
_____________
Code for TallyGenerator -
I tried to fit in your shoes for once, and I didn't like it.
N 56°04'39.16"
E 12°55'05.25" -
And I also have answered this question before, so OP would be able to GOOGLE and find what he want.
N 56°04'39.16"
E 12°55'05.25" -
Heh... I see what you're getting at and I agree...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is a valid solution, Sergiy, so you have evidence that I not only suggest better alternatives and that I can produce the wanted result as well according to OP problem statement.
If you bothered to use GOOGLE, you too would have found this solution.
I don't like the fact that Books Online never shows an example where the algorithm checks for circular reference!
So I included it in my algorithm below. The function returns NULL if a circular reference is found for a category, or a category do not have a [grand]parent with parentid 0 as a top level. It also returns NULL if the provided CategoryID is not found.
-- Create sample data
CREATE TABLE [SAMPLE] (
CATID INT,
PARENTID INT,
CATEGORYNAME VARCHAR(30))
INSERT [SAMPLE]
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'
UNION ALL
SELECT 96,
96,
'Peso0'
UNION ALL
SELECT 97,
40,
'Peso1'
UNION ALL
SELECT 98,
99,
'Peso2'
UNION ALL
SELECT 99,
98,
'Peso3'
UNION ALL
SELECT 23,
20,
'ACER'
GO
CREATE FUNCTION DBO.FNGETLVLNO
(@CategoryID INT)
RETURNS INT
AS
BEGIN
DECLARE @Generation INT
DECLARE @Depth TABLE(
GENERATION INT,
CATID INT
)
SET @Generation = 0
INSERT @Depth
SELECT 0,
@CategoryID
WHILE @@ROWCOUNT > 0
BEGIN
SELECT @CategoryID = CATID,
@Generation = @Generation + 1
FROM @Depth
WHERE GENERATION = @Generation
INSERT @Depth
SELECT @Generation,
S.PARENTID
FROM [SAMPLE] AS S
WHERE S.CATID = @CategoryID
AND NOT EXISTS (SELECT *
FROM @Depth AS X
WHERE X.CATID = S.PARENTID)
END
SELECT @CategoryID = MIN(CATID),
@Generation = COUNT(* )
FROM @Depth
RETURN CASE
WHEN @CategoryID > 0 THEN NULL
ELSE @Generation - 2
END
END
GO
SELECT CATID,
PARENTID,
CATEGORYNAME,
DBO.FNGETLVLNO(CATID) AS LEVEL
FROM SAMPLE
ORDER BY CATID
SELECT DBO.FNGETLVLNO(230704434) AS LEVEL
DROP FUNCTION DBO.FNGETLVLNO
DROP TABLE SAMPLE
N 56°04'39.16"
E 12°55'05.25" -
http://support.microsoft.com/kb/248915
http://msdn2.microsoft.com/en-US/library/aa172799(SQL.80).aspx
N 56°04'39.16"
E 12°55'05.25" -
Vamshi, you can omit the LEFT JOIN part and the WHERE ... IS NULL part, if there are some contraints prohibiting circular reference in your table.
N 56°04'39.16"
E 12°55'05.25" -
I just love people who take the effort to post feedback to their problems and the suggestions they get!
N 56°04'39.16"
E 12°55'05.25" -
Only if you post actual data from your table, and the code used for your function.
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply