I have a table with a sort of fathe

[netangel]

I have a table with a sort of father-son relation

TABLE1
-----------
IDObject
IDObjectFather
Description

Is it possible to list all data, adding a colunm with the level of the current object (number of 'fathers' and 'father or fathers')?

IE:

1, NULL, A
2 , NULL, B
3, 1, C
4, 1, D
5, 3, E
6, 3, F
7, 6, G

I want:

1, NULL, A, 0
2 , NULL, B, 0
3, 1, C, 1
4, 1, D, 1
5, 3, E, 2
6, 3, F, 2
7, 6, G, 3

I know I do it using a cursor, but I think it is too slow (I have thousands of entries) and also not very pratic.



NetAngel

 

[swampBoogie]

Something like

declare @level,@rows int
set @level = 0
update table1 set level = @level where idObjectFather is null
set @rows = @@rowcount
while @rows <> 0
begin
      set @level = @level + 1
      update table1 set level = @level
       where idObjectFather in 
  (select idObject from table1 where level = @level - 1)
      set @rows = @@rowcount
end

 

[netangel]

Is there a way of doing it without the cursor?
I must set it directly into a table, so, it should be a select statment.
I'd like to avoid saving that data in the table for the obvious reason: dificulty in mantaining correct data (all it takes is an update on a father node and all the child will have to be refreshed).

The only way to use a cursor is with a stored procedure that returns a table, and I can't use sp's in my database.


NetAngel