0

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.

2

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

|improve this answer|||||
  • Great answer, thank you so much ... and thanks for the tips! – Mister T Jun 12 '19 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.