Recursion Problem in sql

[pacificpear]

I am using SQL Server

I have a view name called " replica"

A B C D
000185700681 001 000185701034 001
000185700681 001 000185701034 001
000185700681 001 000185701034 001
000185700681 002 000185701034 002
000185700681 002 000185701034 002
000185700681 003 000185701034 003
000185700681 003 000185701034 003
000185700681 004 000185701034 004
000185700681 004 000185701034 004
000185700681 005 000185701034 005
000185700681 005 000185701034 005
000185700681 006 000185701034 006
000185700681 006 000185701034 006


My question is:
result
1.Add another column with name E
2.We have to check if the combination of Column C and Column D is there in the combination of Column A and Column B if it does not exist then print in the column D number into column E
IF it does exiest then check teh coresponding two column and check again and print the last combination column D in column E


Can anybody help

PP

 

[vongrunt]

If I understand correctly, for each row described with (C, D), you want to find root ancestor described with (A, B) and store only it's B (D) value to E. Kind of weird requirement - and posted sample data is not good for testing because A and C are always different - but anyway.

Recursion can be replaced with looping. Add two columns (C1 and D1) and fill them initially with (C, D). Then replace them with (A, B) for rows that have a parent. Repeat until all (C1, D1) pairs have no parents. Something like:

select A, B, C, D, C as C1, D as D1
into #blah
from replica

while @@rowcount > 0
begin
	update X
	set C1 = Y.A, D1=Y.B
	from #blah X
	inner join #blah Y on X.C=Y.A and X.D=Y.B
end

select A, B, C, D, D1 as E from #blah
drop table #blah

Btw. if one or more rows have identical A/C and B/D values (A=C and B=D) loop will never end.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)