HELP, cannot get my recursive query to show TREE

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 2005 Forums

Transact-SQL (2005)

HELP, cannot get my recursive query to show TREE

Author

Topic

udelojf
Starting Member

2 Posts

Posted - 2007-03-16 : 10:47:12

My Table Structure

Category_ID Number
Parent_ID Number <----Category_ID reports to this colum
Category_Name Varchar....

MY QUERY <---I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy

My OUTPUT============

All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.

Can you help me please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 10:48:24

I think you must have a WHERE clause in the first SELECT within CTE, getting only parent categories.

Peter Larsson
Helsingborg, Sweden

udelojf
Starting Member

2 Posts

Posted - 2007-03-16 : 14:49:47

Just tried that, doesnt work. Thanks anyway. Any other suggestion?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources