How to use CTE to match parent-child relationships?

Question

Let's say that I have a table of elements representing tree-structured data, and I would like to keep track of it up until I get to the node vertex marked parent_id of NULL. What does MS SQL CTE (common table expression) look like ?

For example, if I got the path to the top from Bender , it would look like

Comedy

Futurama

Bender

Thanks, and here are some sample data:

DECLARE @t Table(id int, description varchar(50), parent_id int)

INSERT INTO @T 
SELECT 1, 'Comedy', null UNION 
SELECT 2, 'Futurama', 1 UNION
SELECT 3, 'Dr. Zoidberg', 2 UNION 
SELECT 4, 'Bender', 2 UNION
SELECT 5, 'Stand-up', 1 UNION
SELECT 6, 'Unfunny', 5 UNION
SELECT 7, 'Dane Cook', 6

Answer 1

It should look like this:

declare @desc varchar(50)
set @desc = 'Bender'

;with Parentage as
(
    select * from @t where description = @desc
    union all

    select t.* 
    from @t t
    inner join Parentage p
        on t.id = p.parent_id
)
select * from Parentage
order by id asc --sorts it root-first