SQL Server 2012 – Retrieve All Nodes from a Hierarchy Including Non-Matching Parents

sql-server-2012

I have a table similar to this structure:

TaskId:  int PK
AssignedTo: varchar(50)
ParentTaskId: int FK nullable

ParentTaskId references TaskId.

The data in the table looks similar to this:

TaskId     |     AssignedTo     |    ParentTaskId

1                Jim                 NULL
2                Jim                 1
3                Joe                 2
4                John                NULL
...

The crux of my issue is this: I need to query the table for all tasks assigned to Joe (which would return TaskId 3), but I also need to show the hierarchy for the found task, so I need to return Tasks 1, 2, and 3 from the above table.

I'm lost as to how do pull this data from SQL, either with one query or multiple queries.

Help please! Using SQL Server 2012.

Best Answer

For future posts, it's much better to provide CREATE TABLE and INSERT statements like:

create table tasks
(TaskId int not null primary key
,AssignedTo char(5) not null
,ParentTaskId int references tasks (taskid));

insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (1, 'Jim', NULL);
insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (2, 'Jim', 1);
insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (3, 'Joe', 2);
insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (4, 'John', NULL);

That said, you can use a recursive common table expression (CTE) as in:

with tree (ancestortaskid) as (
    select taskid from tasks where assignedto = 'Joe'
    union all
    select ParentTaskId from tree t join tasks s 
        on t.ancestortaskid = s.taskid
)
select t.ancestortaskid 
from tree;

I only had sql-server2017 to test with, but afaik, recursive cte should be availible in 2008 as well

Related Question