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.
