Recursion again - 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)

Recursion again

Author

Topic

vladibo
Starting Member

11 Posts

Posted - 2004-06-07 : 10:26:36

Hello,

I have table that links recursively in order to create hierarchical structure like this:

CREATE TABLE category(
category_id int,
category varcher(255),
parent_id int);

With the help of this beautiful forum I have found how to get all the parent IDs for a specific record. Its like this:

CREATE PROCEDURE pr_GetAllParentIds
@category_id int
AS

Declare @parent_id int
Declare @temp table(cat_id int)

SELECT @parent_id=parent_id FROM category WHERE category_id=@category_id
IF @@ROWCOUNT>0
BEGIN
INSERT INTO @temp VALUES (@parent_id)
SELECT @parent_id=parent_id FROM category WHERE category_id=@category_id
END

SELECT * FROM @temp
GO

But I don't know how to get all the child records for a specific category

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-07 : 11:07:59

insert @temp select @category_id
while @@ROWCOUNT>0
BEGIN
INSERT @temp
SELECT distinct c.parent_id
FROM category c
left join @temp t
on c.parent_id = t.cat_id
WHERE t.cat_id is null
END

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources