MS SQL Recursion problem

[elauri]

I have following tables
table1
nr_key1
nr_dad1

table2
nr_key2
value
(repeated ~n times with same nr_key2 and different value)

in table2 nr_key1 = nr_key2

After I have iterated through nr_key1 I want to use recursion to select nr_key1 = nr_dad1 and iterate again table2 with the new nr_key1. This should be repeated as long as nr_dad1 is null. I have succesfully got the result but only if nr_key1 = nr_key2 contains values. If table2 is empty for the current nr_key1 my recursion is broken. I receive error if I try to use outer join in my recursion. Also my current solution is quite slow, because I am iterating the whole hierarchy again from each value found in table2. Is it possible to implement some sort of a check?

Maybe my approach is wrong? Any suggestions will be highly appreciated thanks.

with test (NR_KEY1, NR_DAD1, VALUE, leveli)  
AS
(
select a.NR_KEY1, a.NR_DAD1, VALUE,
1 as leveli
from table1
right outer join table2 a ON
NR_KEY2 = a.NR_KEY1 where a.NR_KEY1 = 10020

union all

select a.NR_KEY1, a.NR_DAD1, e.VALUE, 
eh.leveli + 1 as leveli
from table1 e
	inner join table2 a ON
	e.NR_KEY2 = a.NR_KEY1
		inner join test eh ON 
			e.NR_KEY2 = eh.NR_DAD1
				where e.NR_KEY2 = eh.NR_DAD1
)
select * from test order by leveli

[huangzhi]

Can u give some data and the result you want?
or you can see http://www.daniweb.com/forums/thread114466.html.

[elauri]

I shamelessly copied your code to make a example :)

CREATE TABLE #tmpName (ID int, [Name] varchar(255))
CREATE TABLE #tmpRelation (ID int, ID_Parent int)

INSERT #tmpName (ID, [Name]) select 1, 'value1'
INSERT #tmpName (ID, [Name]) select 2, 'value2'
INSERT #tmpName (ID, [Name]) select 2, 'value3'
INSERT #tmpName (ID, [Name]) select 2, 'value4'
INSERT #tmpName (ID, [Name]) select 4, 'value5'

INSERT #tmpRelation (ID, ID_Parent) select 1, 2
INSERT #tmpRelation (ID, ID_Parent) select 2, 3
INSERT #tmpRelation (ID, ID_Parent) select 3, 4
INSERT #tmpRelation (ID, ID_Parent) select 4, NULL
INSERT #tmpRelation (ID, ID_Parent) select 5, 1

I would like a following result:
nr;value;dad;level
1;value1;2;1
2;value2;3;2
2;value3;3;2
4;value4;null;4

[elauri]

Sorry I forgot to mention the selection. For the above result I would select id 1 from the tmpRelation table and also there was a mistake in the result.

I would like a following result:
nr;value;dad;level
1;value1;2;1
2;value2;3;2
2;value3;3;2
2;value4;3;2
*3;null;4;3
4;value5;null;4

*(3 is not displayed because nothing is found, not problem if it has to be displayed with null value)

[huangzhi]

Try code below

set nocount on
CREATE TABLE #tmpName (ID int, [Name] varchar(255))
CREATE TABLE #tmpRelation (ID int, ID_Parent int) 

INSERT #tmpName (ID, [Name]) select 1, 'value1'
INSERT #tmpName (ID, [Name]) select 2, 'value2'
INSERT #tmpName (ID, [Name]) select 2, 'value3'
INSERT #tmpName (ID, [Name]) select 2, 'value4'
INSERT #tmpName (ID, [Name]) select 4, 'value5'

INSERT #tmpRelation (ID, ID_Parent) select 1, 2
INSERT #tmpRelation (ID, ID_Parent) select 2, 3
INSERT #tmpRelation (ID, ID_Parent) select 3, 4
INSERT #tmpRelation (ID, ID_Parent) select 4, NULL
INSERT #tmpRelation (ID, ID_Parent) select 5, 1

declare @ID_Start int, @Level int
select @Level = 0
select @ID_Start = 1

create table #tmpResult (ID int, ID_Parent int, [Level] int)

while not @ID_Start is null
begin
  select @Level = @Level + 1

  insert #tmpResult (ID, ID_Parent, [Level])
    select R.ID, R.ID_Parent, @Level
      from #tmpRelation R
      where ID = @ID_Start

  select @ID_Start = ID_Parent from #tmpRelation where ID = @ID_Start
end

--> NR = 3 display
select	R.ID as NR, N.Name as Value, ID_Parent as Dad, [Level]
  from	#tmpResult R left join #tmpName N on
	R.ID = N.ID

--> NR = 3 not display
select	R.ID as NR, N.Name as Value, ID_Parent as Dad, [Level]
  from	#tmpResult R inner join #tmpName N on
	R.ID = N.ID

drop table #tmpResult
drop table #tmpName
drop table #tmpRelation