quote:

---

I only have one problem: whenever I tried to enter the drummer's name into the table, the data just disappeared. It's like it spontaneously combusted or something.

---

create table #adjacency (
    parent varchar(20) not null,
    child  varchar(20) not null )

insert #adjacency
select 'mona','lisa' union
select 'mona','joe' union
select 'joe','timmy'

create table #nestedset (
    item varchar(20),
    lft int,
    rgt int )

insert #nestedset (item,lft,rgt)
select ?????

create table tree (
    emp char (10) not null,
    boss char (10) )

insert tree values ('Jerry',NULL)
insert tree values ('Bert','Jerry')
insert tree values ('Chuck','Jerry')
insert tree values ('Donna','Chuck')
insert tree values ('Eddie','Chuck')
insert tree values ('Fred','Chuck')
insert tree values ('Page47',NULL)
insert tree values ('Sloan','Page47')

emp        lft         rgt         
---------- ----------- ----------- 
Jerry      1           12
Bert       2           3
Chuck      4           11
Donna      5           6
Eddie      7           8
Fred       9           10
Page47    13           16
Sloan     14           15

quote:

---

The woods are lovely, dark and deep.
But I have promises to keep,
And miles to go before I sleep,
And miles to go before I sleep.

---

create table adjmodel (
    emp char (10) not null,
    boss char (10) )
go

create table setmodel (
    emp char(10),
    boss char(10),
    lft integer,
    rgt integer )
go

create view allpairs
as
select
    t.boss as superior,
    t.emp as subordinate
from
    setmodel t
where
    exists ( select 1
             from setmodel
             where boss = t.emp) and
    t.boss <> t.emp
go

create view leftmostpairs
as
select distinct
    superior,
    (select
         min(subordinate)
     from
         allpairs
     where
         a.superior = superior) as subordinate
from
    allpairs a
where
    superior = (select min(superior)
                from allpairs)
go

create index idx_adjmodel_boss on adjmodel(boss)
create clustered index idx_adjmodel_emp on adjmodel(emp)
create index idx_setmodel_empboss on setmodel(emp,boss)
create index idx_setmodel_lftrgt on setmodel(lft,rgt)
go

insert adjmodel values ('Jerry',NULL)
insert adjmodel values ('Bert','Jerry')
insert adjmodel values ('Chuck','Jerry')
insert adjmodel values ('Page47',NULL)
insert adjmodel values ('Donna','Chuck')
insert adjmodel values ('Eddie','Chuck')
insert adjmodel values ('Fred','Chuck')
insert adjmodel values ('Sloan','Page47')
go

create proc adjmodelMerge
    @superior char(10),
    @subordinate char(10)
as

declare
    @size int,
    @insert_point int

select
    @size = 2 * ( select
                      count(*)
                  from
                      setmodel
                  where
                      emp = @subordinate ),
    @insert_point = ( select
                          min (lft)
                      from
                          setmodel
                      where
                          emp = @subordinate and
                          boss = @superior ) - 1

update
    setmodel
set
    boss = case
               when boss = @subordinate then case
                                                 when emp = @subordinate then null
                                                 else @superior
                                             end
               else boss
           end,
    lft = case
              when (boss = @superior and lft > @size) then lft + @size
              else case
                       when (boss = @subordinate and emp <> @subordinate) then lft + @insert_point
                       else lft
                   end
          end,
    rgt = case
              when (boss = @superior and rgt > @size) then rgt + @size + 2
              else case
                       when (boss = @subordinate and emp <> @subordinate) then rgt + @insert_point
                       else rgt
                   end
              end
where
    boss in (@superior, @subordinate)

delete
    setmodel
where
    boss is null or
    emp is null
go

insert setmodel
select distinct
    t.boss,
    t.boss,
    1,
    2*(select count(*) + 1 from adjmodel where t.boss = boss)
from
    adjmodel t

insert setmodel
select distinct
    t.emp,
    t.boss,
    2*(select count(distinct emp) from adjmodel where emp <= t.emp and t.boss = boss), --in (emp,boss)),
    2*(select count(distinct emp) from adjmodel where emp <= t.emp and t.boss = boss) + 1 --in (emp,boss)) + 1
from
    adjmodel t

delete setmodel
where boss is null or emp is null

declare @boss char(10), @emp char(10)
while exists (
        select 1
        from
            leftmostpairs)
begin
    select
        @boss = superior,
        @emp = subordinate
    from
        leftmostpairs

    exec adjmodelmerge
        @superior = @boss,
        @subordinate = @emp

end

--and the proof
select * from setmodel order by boss,lft