Urgent Help: tree structure - 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

SQL Server Development (2000)

Urgent Help: tree structure

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-02-07 : 13:48:38

I have a table of structure

Parent_id child_id p_count
A1234 B2345 1
A1234 B4567 4
A1234 B5678 5
A1234 C4567 6
B2345 D7890 2
B2345 E4567 3
B5678 W345 10
C4567 E7890 7
W345 X8907 9
E4567 Z2345 10
There are thousands of records and level is un-known. Only when there is no record to return for all child_id as parent_id that is end of the one parentid. Please help on this.
I need to get all the child when given a parentid,like in this eg:A1234

ModemRat
Starting Member

4 Posts

Posted - 2007-02-07 : 18:35:12

I wrote a recursive function to get all the SubCategories from a given category from a table with a similar structure. Here's my code, you can get the idea from it:


CREATE FUNCTION [dbo].[GetSubCategories]
(
@CategoryId int
)
RETURNS @Categories TABLE 
(
CategoryId int
)
AS
BEGIN

	-- Get Immediate Sub Categories
	insert into @Categories
	select KBCategory.KBCategoryId
	from KBCategory 
	where KBCategory.ParentId = @CategoryId


	-- Recursive Call to get sub categories for each SubCategory
	declare @tempTable table (CategoryId int)
	declare @myCategoryId int
	DECLARE Category_Cursor CURSOR FOR SELECT CategoryId FROM @Categories

	OPEN Category_Cursor
	FETCH NEXT FROM Category_Cursor into @myCategoryId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		insert into @Categories
		select CategoryId
		from dbo.GetSubCategories(@myCategoryId)		

		FETCH NEXT FROM Category_Cursor into @myCategoryId 
	END
	CLOSE Category_Cursor
	DEALLOCATE Category_Cursor

	RETURN 
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources