Category Hiearchy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Category Hiearchy

Author

Topic

vk59
Starting Member

38 Posts

Posted - 2005-09-15 : 06:23:57

we have a table to define Categories and Sub categories as below,

CategoryId-->CategoryName-->ParentCategory
1-->Beverages -->NULL
2-->Food -->NULL
3-->Cool Drinks -->1
4-->Hard Drinks -->1
5-->Poultry -->2
6-->Sea Food -->2
7-->Chicken -->5
8-->Mutton -->5
9-->Prawn -->6
10-->Fish -->6

We need a query wherein when an ID of sub category is passed, it should return all the parent Category Ids in the hierarchy.
For E.g when Category id 10 is passed, query should return categories with IDs 10,6 and 2

Thanks in advance
VK59

`

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-15 : 06:56:20

read this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=2828

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 07:17:32

Refer these also
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp

Madhivanan

Failing to plan is Planning to fail

rythm123us
Starting Member

27 Posts

Posted - 2005-09-17 : 10:00:56

Here is something I use:

DECLARE @Category TABLE(CategoryID INT, ParentID INT, CategoryText VARCHAR(55), Lineage VARCHAR(55), SortOrder INT)

INSERT @Category(CategoryID, ParentID, CategoryText, Lineage, SortOrder)
SELECT 1, 0, 'All', '1', 0 UNION ALL
SELECT 2, 1, 'Beer', '1.1', 1 UNION ALL
SELECT 3, 2, 'Domestic', '1.1.1', 2 UNION ALL
SELECT 37, 1, 'Wine', '1.4', 38 UNION ALL
SELECT 38, 37, 'Domestic', '1.4.1', 39 UNION ALL
SELECT 39, 38, 'California', '1.4.1.1', 40

-- Get all the Children
SELECT C1.*
FROM @Category C1 INNER JOIN
@Category C2 ON C2.Lineage = SUBSTRING(C1.Lineage, 1, LEN(C2.Lineage))
WHERE C2.CategoryID = 37

-- Get all the Parents
SELECT C1.*
FROM @Category C1 INNER JOIN
@Category C2 ON C1.Lineage = SUBSTRING(C2.Lineage, 1, LEN(C1.Lineage))
WHERE C2.CategoryID = 37

The necessary Field here is Lineage

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources