All of the recursive CTE examples I can find use essentially the same scenario. They all traverse up or down an org chart. My CTE works great for parent/child relationships that are one-to-one, like where one employee has one manager, but I have a table where a child can have multiple parents. What I want is a disctinct list of parent IDs stemming from one child.
If you're interested in specifics, I'm recursing through the predecessors of a particular task in an MS Project file. I want to start with a particular milestone and trace all the way up to the top of the file in order to find any predecessors that may be affecting the milestone. As you may know, a task can have multiple predecessors.
My relationships look like this:
tblTasks
child parent
3 1
16 1
25 1
25 3
25 16
26 1
26 3
27 25
27 26
Here is my CTE:
;WITH ProjectTrace(Task)
AS
(
-- Anchor member definition (This is task 27)
SELECT t.parent AS Task
FROM #tblTasks t
WHERE t.child = 27
UNION ALL
-- Recursive member definition (This is everything that hooks into 27 via predecessors)
SELECT t.parent AS Task
FROM #tblTasks t
INNER JOIN ProjectTrace trace
ON t.child = trace.Task
)
SELECT * FROM ProjectTrace ORDER BY Task
I want to supply task #27 to the query and get only 1,3,16,25,26 in my resultset. However, because of the way the recursion works, my resultset looks like this:
Task
1
1
1
1
1
3
3
16
25
26
If you look at the relationships, I guess that makes sense. I can always change the select a the end to a select distinct, but when I get really deep in the project, say task number 500, it returns millions of records.
What might I be doing wrong?
I think distinct is good way to do this. You can also check iterative insert solution:
declare @Temp table(Task int primary key)
insert into @Temp
select distinct parent from Table1 where child = 27
while 1 = 1
begin
insert into @Temp
select distinct T.parent
from Table1 as T
where
exists (select * from @Temp as TT where TT.Task = T.child) and
not exists (select * from @Temp as TT where TT.Task = T.parent)
if @@rowcount = 0 break
end
select * from @Temp
don't know if it would be faster, check it by yourself.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments