QPR
asked on
Recursive function until result = 0
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
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
ASKER
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 :)
ASKER
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?
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?
Hi QPR,
I've taken Pinal's final query and added a string to it.
This builds a reportline string showing the reporting line.
The key is that the recursive query references
Hope this clarifies things.
Regards
David
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
;
This builds a reportline string showing the reporting line.
The key is that the recursive query references
Hope this clarifies things.
Regards
David
sorry
The key is that the recursive query references the CTE
eg
el.EmpLevel + 1
Regards
DAvid
The key is that the recursive query references the CTE
eg
el.EmpLevel + 1
Regards
DAvid
ASKER
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
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"
Below is an image below that shows some sample data
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"
Hi QPR,
Does this help?
David
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
;
RegardsDavid
ASKER
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"
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
David, you are the man!
If you are ever in this neck of the woods I owe you a case of beer!
If you are ever in this neck of the woods I owe you a case of beer!
ASKER
Excellent answer and beyond the call of duty!
ASKER
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
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
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