MS SQL

MS SQL Recursion problem

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Nov 2009

Posts: 6

Reputation: elauri is an unknown quantity at this point

Solved Threads: 0

elauri

Offline

Newbie Poster

MS SQL Recursion problem

Nov 8th, 2009

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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

Nov 9th, 2009

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

Hence Wijaya
www.ex-Soft.tk

•

Join Date: Nov 2009

Posts: 6

Reputation: elauri is an unknown quantity at this point

Solved Threads: 0

elauri

Offline

Newbie Poster

Nov 10th, 2009

I shamelessly copied your code to make a example

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

Last edited by elauri; Nov 10th, 2009 at 2:58 am.

•

Join Date: Nov 2009

Posts: 6

Reputation: elauri is an unknown quantity at this point

Solved Threads: 0

elauri

Offline

Newbie Poster

Nov 10th, 2009

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)

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

Nov 10th, 2009

Try code below

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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