0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/6881759/sql-server-2008-hierarchical-queries

Q&A for pro webmasters

-- Enter Query Title
-- Enter Query Description


    
    
    declare @t table (id int, type varchar(10), name varchar(50), parentid int)
    insert @t
              select 1, 'COUNTRY', 'AU', null
    union all select 2, 'STATE', 'Guiness State', 1
    union all select 3, 'STATE', 'Pilsner State', 1
    union all select 4, 'REGION', 'Heineken Region', 2
    union all select 5, 'REGION', 'Bud Light Region', 3
    union all select 6, 'TOWN', 'Hoegaarden  Town', 2
    union all select 7, 'TOWN', 'Corona Town', 2
    union all select 8, 'TOWN', 'Leffe Town', 3
    
    ; with  rec as
            (
            select  *
            from    @t
            where   name = 'Guiness State'
            union all
            select  t.*
            from    rec
            join    @t t 
            on      rec.id = t.parentid
            )
    select  *
    from    rec

fork query

created jul 30 11 jon.doe6270