Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Recursive function until result = 0

Posted on 2013-01-16
12
Medium Priority
?
256 Views
Last Modified: 2013-01-22
We have a table that has an ID and a ParentID.
I need to create a function that takes an ID and will build a heirarchy going up the tree until ParentID = 0 (top of the tree)

We might end up with 23,577,901,11,0

So we have passed 23 to the function and looped through 3 parents before stopping as "11" does not have a parent. 0 is held in ParentID to denote no parentID
0
Comment
Question by:QPR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 38785315
Hi QPR,

Try this link
http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

The recursive hierarchy is solved very nicely with a CTE - Common Table Expression.

HTH
  David
0
 
LVL 29

Author Comment

by:QPR
ID: 38788904
Thanks. Looks a bit heavy for 8am before my first coffee. I'll give it a read and see if I can apply it to my scenario :)
0
 
LVL 29

Author Comment

by:QPR
ID: 38802949
Thanks, I'm getting the concept of the CTE but am having problems visualising what I need to achieve let alone code it!

I'm basically trying to build a file path
A record has a recordID and (until it reaches the top level) has a parentID


I need to take the initial record ID and string together ALL it's parentID until ParentID = 0.
At this point I will concatonate StructureID to the end of the string

I need to requery the records table each time ParentID <> 0
ParentID will become the recordID for subsequent selects.
As soon as parentID becomes 0 then we select structureID

So (assuming a given record has 3 parents) we would end up with

recordID + '/' + parentID '/' + parentID '/' + parentID + '/' + StructureID



get the parentID

select parentID from the table
where recordID = 123

repeat and concatonate until parentID = 0
Take built string and add '/' + StructureID where recordID = last found ParentID

Does that make sense?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 35

Expert Comment

by:David Todd
ID: 38803126
Hi QPR,

I've taken Pinal's final query and added a string to it.

use tempdb
go

if object_id( N'tempdb..#e', N'U' ) is not null 
	drop table #e;
	
create table #e(
	EmpID int identity( 1, 1 )
	, FirstName varchar( 30 )
	, LastName varchar( 30 )
	, ManagerID int
	)
;

insert #e( FirstName, LastName, ManagerID )
	values( 'Alex', 'Adams', 11 )
	, ( 'Barry', 'Brown', 11 )
	, ( 'Lee', 'Osako', 11 )
	, ( 'David', 'Kennson', 11 )
	, ( 'Eric', 'Bender', 11 )
	, ( 'Lisa', 'Kendall', 4 )
	, ( 'David', 'Lonning', 11 )
	, ( 'John', 'Marshbank', 4 )
	, ( 'James', 'Newton', 3 )
	, ( 'Terry', 'O''Haire', 3 )
	, ( 'Sally', 'Smith', null )
	, ( 'Barbara', 'O''Neil', 4 )
	, ( 'Phil', 'Wilconkinski', 4 )
;
	
select *
from #e
;

select 
	Boss.EmpID
	, Boss.FirstName
	, Boss.LastName
	, Boss.ManagerID
from #e as Boss
where
	Boss.ManagerID is null
;

select
	Emp.EmpID
	, Emp.FirstName
	, Emp.LastName
	, Emp.ManagerID
from #e as Emp
where
	Emp.ManagerID is not null
;

with EmployeeList as (
	select
		Boss.EmpID
		, Boss.FirstName
		, Boss.LastName
		, Boss.ManagerID
		, 1 as EmplLevel
		, convert( varchar( 100 ), '' ) as ReportLine
	from #e as Boss
	where
		Boss.ManagerID is null
	
	union all

	select
		Emp.EmpID
		, Emp.FirstName
		, Emp.LastName
		, Emp.ManagerID
		, el.EmplLevel + 1
		, convert( varchar( 100 ), el.FirstName + '/' + el.ReportLine )
	from #e as Emp
	inner join EmployeeList as el
		on el.EmpID = emp.ManagerID
	where
		Emp.ManagerID is not null
	)

select * from EmployeeList
;

Open in new window


This builds a reportline string showing the reporting line.

The key is that the recursive query references

Hope this clarifies things.

Regards
  David
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38803132
sorry

The key is that the recursive query references the CTE
eg
el.EmpLevel + 1

Regards
  DAvid
0
 
LVL 29

Author Comment

by:QPR
ID: 38803378
Thanks, I'm "sort of" following it but not having much luck applying it to my scenario.
Below is an image below that shows some sample data

Heirarchy
So we pass recordID 123 to our t-sql and see it has a ParentID > 0 (we store the title of 123)
We then requery the ParentID where recordID = 456 (we store the title of 456)
If parentID is > 0 then we repeat the above step until ParentID = 0 (storing the title value each time)
When ParentID = 0 we take the value of StructureID, requery where RecordID = 99 and store the title

We end up with "I am a record\I am a folder\I am the top level folder\I am a place in the org chart"
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38803510
Hi QPR,

Does this help?

with EmployeeList as (
	select
		Boss.EmpID
		, Boss.FirstName
		, Boss.LastName
		, Boss.ManagerID
		, 1 as EmplLevel
		, convert( varchar( 100 ), '' ) as ReportLine
	from #e as Boss
	where
		Boss.ManagerID is null
	
	union all

	select
		Emp.EmpID
		, Emp.FirstName
		, Emp.LastName
		, Emp.ManagerID
		, el.EmplLevel + 1
		, convert( varchar( 100 ), el.ReportLine + el.FirstName + '/' )
	from #e as Emp
	inner join EmployeeList as el
		on el.EmpID = emp.ManagerID
	where
		Emp.ManagerID is not null
	)

select * from EmployeeList
;

Open in new window

Regards
  David
0
 
LVL 29

Author Comment

by:QPR
ID: 38803775
I think I may have to do some serious reading up on CTEs before I'm able to convert your example/sample to my needs :)
I do appreciate all the effort you've put in though.

Would it be a liberty to ask you to have a go using the sample data shown in the image?
Remembering that a ParentID will always have a value... but when that value is 0 we ignore the ParentID and *instead* requery using the ID in StructureID as opposed to ending

In the case of the sample shown we'd have one string "/" delimited that would be a horizontal version of the vertical values in "title"
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 38803847
Hi QPR,

use tempdb
go

if object_id( N'tempdb..#r', N'U' ) is not null 
	drop table #r;
	
create table #r(
	RecordID int
	, ParentID int
	, StructureID int
	, Title varchar( 100 )
	)
;

insert #r( RecordID, ParentID, StructureID, Title )
	values( 123, 456, null, 'I am a record' )
	, ( 456, 789, null, 'I am a folder' )
	, ( 789, 0 , 99, 'I am the top level folder' )
	, ( 99, null, null, 'I am a place in the org chart' )
;

with RecordList as (
	select
		t.RecordID
		, t.ParentID
		, t.StructureID
		, t.Title
		--, 1 as EmplLevel
		, convert( varchar( 100 ), '/' + t.Title ) as ReportLine
	from #r as t
	where
		t.ParentID is null
		and t.StructureID is null
	
	union all

	select
		t.RecordID
		, t.ParentID
		, t.StructureID
		, t.Title
		, convert( varchar( 100 ), '/' + t.Title + rl.ReportLine )
	from #r as t
	inner join RecordList as rl
		on rl.RecordID = isnull( nullif( t.ParentID, 0 ), t.StructureID )
	where
		isnull( nullif( t.ParentID, 0 ), t.StructureID ) is not null
	)

select *
from RecordList
;

Open in new window


HTH
  David
0
 
LVL 29

Author Comment

by:QPR
ID: 38803973
David, you are the man!
If you are ever in this neck of the woods I owe you a case of beer!
0
 
LVL 29

Author Closing Comment

by:QPR
ID: 38804007
Excellent answer and beyond the call of duty!
0
 
LVL 29

Author Comment

by:QPR
ID: 38807070
oops 1 tweak required.
ParentID or StructureID are never null. They contain a 0 (int) when they are n/a

So when there is no parent (meaning next level up is structure) then ParentID = 0 and StructureID = n

Vice versa when the next level up is a parent then parentID = n and structureID = 0
The WHERE in the CTE creation says "is null" so I assume I just change these to "= 0"?

But the bit...
inner join RecordList as rl
            on rl.RecordID = isnull( nullif( t.ParentID, 0 ), t.StructureID )
      where
            isnull( nullif( t.ParentID, 0 ), t.StructureID ) is not null
      )


seems trickier as we are testing for null rather than 0 or n
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question