Ad:

MS SQL Discussion Thread
Marked Solved
Views: 1309

Nov 8th, 2009

MS SQL Recursion problem

Expand Post »

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.

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

Similar Threads

sql query problem with MS Access and C# in C#
SQL in VB.NET problem in VB.NET
Problem with php and SQL in PHP
another MS SQL timeout problem in MS SQL
Weird ASP/SQL Problem in ASP

elauri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 9th, 2009

Re: MS SQL Recursion problem

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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

Permalink

Nov 10th, 2009

Re: MS SQL Recursion problem

I shamelessly copied your code to make a example

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.

elauri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 10th, 2009

Re: MS SQL Recursion problem

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)

elauri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Nov 2009

Permalink

Nov 10th, 2009

Re: MS SQL Recursion problem

Try code below

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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Connect by prior command

Next Thread in MS SQL Forum Timeline: SSIS skip bad row

DaniWeb Message

Cancel Changes

User Name
Password