Question about Nested Set Model - Hierarchical Data in SQL Server 2000

Hi,

I would like to convert my adjancey set model to a nested set model with a left and right.

I understood this part.

Root Node is always with a a Left = 1, right = 2*(Select Count(*) from Table)

How do the leaf node get the left and right numbers.

I was reading an article of Joe Celko regarding the Nested Sets in SQL and I could not understand how do the employee below Albert(who is top level manager) get their node numbers.

Adjanceny Model
--
Emp Boss

Albert Null
Bert Albert
Chuck Albert
Donna Chuck
Eddie Chuck
Fred Chuck

Nested Set Model
-
Emp Left Right
Albert 1 12
Bert 2 3
Chuck 4 11
Donna 5 6
Eddie 7 8
Fred 9 10

If anyone could kindly explain this to me, then I can try converting my table
to a nested set model to prevent recursion.

Thanks.

set nocount on
create table #chain
( seq integer not null primary key,
emp char (20),
chain varchar (20),
lft integer,
rgt integer
)

declare @.begDt datetime set @.begDt = getdate();

with organization (depth, emp, boss, chain)
as
( select 1 as depth,
emp,
boss,
cast(rtrim(emp) as varchar (400))
from tree
where boss is null
union all
select a.depth + 1 as depth,
b.emp,
b.boss,
cast (a.chain + ', ' + rtrim(b.emp) as varchar(400))
from organization a
inner join tree b
on a.emp = b.boss
)
insert into #chain (seq, emp, chain)
select row_number () over (order by chain) as seq,
emp,
chain
from organization

update #chain
set lft = 1 + 2 * ( select count(*) from #chain b
where b.seq < a.seq
)
- ( select count(*) from #chain c
where c.seq < a.seq
and ( c.chain = rtrim(c.emp) or
a.chain like '%, ' + rtrim(c.emp) + '%'
)
)
from #chain a

update #chain
set rgt = lft + 1
+ 2 * ( select count(*) from #chain b
where b.seq <> a.seq
and ( b.chain like '%, ' + rtrim(a.emp) + '%' or
a.seq = 1
) )
from #chain a

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

select emp, lft, rgt from #chain

-- - Output: -

-- emp lft rgt
-- -- -- --
-- Albert 1 12
-- Bert 2 3
-- Chuck 4 11
-- Donna 5 6
-- Eddie 7 8
-- Fred 9 10

-- Elapsed Time
--
-- 46

go

drop table #chain
go

|||

Thanks for the post. Can you please let me know the structure of your table Organization so that I can re-create it.

When I run the code at my SQL Server, I see the 2 errors:

Incorrect syntax near the keyword 'with'.

at the line:

with organization (depth, emp, boss, chain)

So I guess that that I do not have the Organization table and hence the error.

2nd error:

'row_number' is not a recognized function name.

at the line below:

select row_number () over (order by chain) as seq,

THanks.