0

Please login or register to vote for this query.

(click on this box to dismiss)

Enter Query Description

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