need special Recursive query. - 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 2005 Forums

Transact-SQL (2005)

need special Recursive query.

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2011-04-04 : 19:21:12

Hi
i have a table named 'Tasks'. each task can be child of another task (this table has self reference constraint)
i want to get all tasks which related to current task. current task maybe the first task (parent task), middle task or child task.
i think the first step is find the first parent task (root task), and then query to all childs of the root task.
how to get this query ?
thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 19:46:59

You can use a recursive CTE to do this. This page has descriptions and examples. http://msdn.microsoft.com/en-us/library/ms186243.aspx

If you run into difficulties, post the DDL for your tables and some sample data and some of the people on this forum would be able to help.

hdv212
Posting Yak Master

140 Posts

Posted - 2011-04-04 : 20:06:28

thanks for reply
this is my sample table with some test data :

CREATE TABLE [dbo].[Tasks](
[TaskID] [int] NOT NULL,
[TaskName] [nvarchar](50) NULL,
[ParentTaskID] [int] NULL,
[TaskBody] [nvarchar](max) NULL,
CONSTRAINT [PK_Tasks] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Tasks] ([TaskID], [TaskName], [ParentTaskID], [TaskBody]) VALUES (1, N'Test 1', NULL, N'this is a first task')
INSERT [dbo].[Tasks] ([TaskID], [TaskName], [ParentTaskID], [TaskBody]) VALUES (2, N'Re : Test 1', 1, N'this is a second task')
INSERT [dbo].[Tasks] ([TaskID], [TaskName], [ParentTaskID], [TaskBody]) VALUES (3, N'Re: Test 1', 1, N'This is a third task')
INSERT [dbo].[Tasks] ([TaskID], [TaskName], [ParentTaskID], [TaskBody]) VALUES (4, N'Re : Test 1', 2, N'This is 4th task')
INSERT [dbo].[Tasks] ([TaskID], [TaskName], [ParentTaskID], [TaskBody]) VALUES (5, N'Re Test 1', 4, N'This is 5th task')
INSERT [dbo].[Tasks] ([TaskID], [TaskName], [ParentTaskID], [TaskBody]) VALUES (6, N'Test 2', NULL, N'This is test 2')
INSERT [dbo].[Tasks] ([TaskID], [TaskName], [ParentTaskID], [TaskBody]) VALUES (7, N'Re : Test 2', 6, N'This is second test 2')
INSERT [dbo].[Tasks] ([TaskID], [TaskName], [ParentTaskID], [TaskBody]) VALUES (8, N'Re : Test 2', 7, N'This is third test 2')
/****** Object: ForeignKey [FK_Tasks_Tasks] Script Date: 04/06/2011 04:33:41 ******/
ALTER TABLE [dbo].[Tasks] WITH CHECK ADD CONSTRAINT [FK_Tasks_Tasks] FOREIGN KEY([ParentTaskID])
REFERENCES [dbo].[Tasks] ([TaskID])
GO
ALTER TABLE [dbo].[Tasks] CHECK CONSTRAINT [FK_Tasks_Tasks]
GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 20:38:21

That is good data that one can look at and work with :--). Here is one way of doing it using recursive CTE. I am looking only for ancestors and descendants, not siblings.

select * from TAsks

declare @TaskId int; set @TaskId = 4;

with ParentCTE as
(
	select *, cast('SELF' as varchar(31)) as Ident from Tasks where TaskId = @TaskId
	
	union all
	
	select t.*,cast('ANCESTOR' as varchar(31)) from Tasks t inner join ParentCTE c on c.ParentTaskId = t.TaskId
),
ChildCTE as
(
	select *, cast('SELF' as varchar(31)) as Ident from Tasks where  TaskId = @TaskId
	
	union all
	
	select t.*, cast('DESCENDANT' as varchar(31)) from Tasks t inner join ChildCTE c on c.TaskId = t.ParentTaskId
)
select * from ParentCTE
union
select * from ChildCTE
order by TaskId;

hdv212
Posting Yak Master

140 Posts

Posted - 2011-04-05 : 05:14:02

Thanks for reply
but this query does not return my expected result.
the first which to be solve is that we must find the root parent of each task.
can u give me this query ?
thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-05 : 07:08:46

If you just want to find the ultimate parent, it can be done in a similar way.


declare @TaskId int; set @TaskId = 4;

with ParentCTE as
(
	select *, 1 as Level from Tasks where TaskId = @TaskId
	
	union all
	
	select t.*, Level+1 as Level from Tasks t inner join ParentCTE c on c.ParentTaskId = t.TaskId
)
select top 1 * from ParentCTE order by Level desc

Now if you wanted to find all the children of the ultimate parent, then you would do this:

declare @TaskId int; set @TaskId = 4;

with ParentCTE as
(
	select *, 1 as Level from Tasks where TaskId = @TaskId
	
	union all
	
	select t.*, Level+1 as Level from Tasks t inner join ParentCTE c on c.ParentTaskId = t.TaskId
),
ChildCTE as
(
	select top 1 * from ParentCTE order by Level desc
	
	union all
	
	select t.*, Level+1 from Tasks t inner join ChildCTE c on c.TaskId = t.ParentTaskId
)
select * from ChildCTE

If you will need to do this kind of thing frequently, look up Recursive CTE's here: http://msdn.microsoft.com/en-us/library/ms186243.aspx They have a nice explanation with examples.

hdv212
Posting Yak Master

140 Posts

Posted - 2011-04-05 : 13:41:15

thanks
your post solved my problem.
thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources