Author |
Topic |
Agenteusa
Starting Member
11 Posts |
Posted - 2011-07-06 : 14:42:24
|
Hello everyone,I´m having a problem building a query/function that has to be recursive on 3 or more levels:I have a table (Dynamics Database)named "businessUnits" which has amongst other fields a field named "businessunitid" and a field named "parentbusinessunitid" and both are of type "uniqueidentifier".I´m getting a variable from a dropdownlist in reporting services that will pass a @businessunitid which is "uniqueidentifier" as well.Now, the table structure means that each business unit present on the table can be either a parent of another unit, a child of another unit, both or neither as you can imagine.My challenge is getting that input from the user I need to build a table that will have all related units to the one the user selected.Let´s say I receive unit X.Y is a parent of X.X has 3 childs A, B and C.And B has another child E.I hope I got the point across. What I guess is I need a recursive query/function or nested while loops that allow me to go through all the records dynamically. Note that I don´t know at the start how many levels the query will have.This will all be inserted into one table for later use.Can anyone help me or give some pointers/tips?Would appreciate if someone could help.Thank you for your time. |
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-07-06 : 15:11:44
|
look into recursive CTE... you'll probably need 2. One for parents, and one for children...CoreyI Has Returned!! |
|
|
Agenteusa
Starting Member
11 Posts |
Posted - 2011-07-07 : 05:51:44
|
What do you mean by 2 CTE`s?You mean using 2 "With [TABLE]...", something like this or would I need more? Sorry for emphasizing but I´m not really used to CTE´s so I have some doubts about how to use them :SUSE AdventureWorks2008R2;GOWITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel FROM dbo.MyEmployees WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 FROM dbo.MyEmployees AS e INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID )SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReportsORDER BY ManagerID;GO |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
|
Agenteusa
Starting Member
11 Posts |
Posted - 2011-07-07 : 06:12:13
|
Well this example is from a Microsoft Reference. :)But I tried to adapt it to what I need and that´s what is confusing me, I can´t seem to get the result I need hence why I was posting here, to see if someone could give me some pointers on how to do it and how many of these would I need and if I need to join them.That is my whole question. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 06:19:52
|
well you just need the children tree plus the parents which will be a single path.What format do you want it in?something likewith cte as(select *, level = 0 from businessUnits where businessunitid = @businessunitidunion allselect b.*, level = level+1 from cte join businessUnits b on b.businessunitid = cte.parentbusinessunitid) ,cte1 as(select *, level = 0 from businessUnits where businessunitid = @businessunitidunion allselect b.*, level = level-1 from cte join businessUnits b on cte.businessunitid = b.parentbusinessunitid)select * from cte.*unionselect * from cte1.*==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Agenteusa
Starting Member
11 Posts |
Posted - 2011-07-07 : 06:37:51
|
I need the result set to be inserted into a Temp table that will be used in the sProc scope. But I guess all I have to do is select from the cte´s. Your code is giving me an error at the end:select * from cte.*unionselect * from cte1.*The "cte´s.*" are giving me a syntax error but I´ll try and work it out to see if I can get it to work. |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-07-07 : 07:21:02
|
try putting a ; in front of the With... and I think the reference in cte1 is wronglike:;with cte as(select *, level = 0 from businessUnits where businessunitid = @businessunitidunion allselect b.*, level = level+1 from businessUnits b join cte on b.businessunitid = cte.parentbusinessunitid) ,cte1 as(select *, level = 0 from businessUnits where businessunitid = @businessunitidunion allselect b.*, level = level-1 from businessUnits b join cte1 on cte1.businessunitid = b.parentbusinessunitid)select * from cte.*unionselect * from cte1.* CoreyI Has Returned!! |
|
|
Agenteusa
Starting Member
11 Posts |
Posted - 2011-07-07 : 09:27:33
|
Ok , I tried your example and it works. The problem is it´s not dynamic. In my case for instance I had to add another "cte2" for it to return the 3rd level of childs (level - 2).Is there a way to identify within a table how many levels of parent-child I will have? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 09:31:06
|
That should deal with any number of levels of children (or parents).level should show which level it is at - positive for children, negative for parents.cte gives all the parents, cte1 gives the children tree.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Agenteusa
Starting Member
11 Posts |
Posted - 2011-07-07 : 09:44:38
|
It gives the children indeed, but doesn´t return the children(s) of the children.I tweaked it a little bit based on another reference and this seems to work for me:WITH CTE AS ( SELECT C.businessunitid, C.parentbusinessunitid, C.Name, 0 AS Level FROM FilteredBusinessUnit C WHERE C.parentbusinessunitid = @businessunit UNION ALL SELECT C.businessunitid, c.parentbusinessunitid, C.Name, Level + 1 FROM FilteredBusinessUnit C INNER JOIN CTE ON CTE.businessunitid = c.parentbusinessunitid ) SELECT * FROM CTE UNION SELECT C.businessunitid, C.parentbusinessunitid, C.Name, 0 FROM FilteredBusinessUnit C WHERE C.businessunitid = @businessunitMight help someone.Thanks for your replies ;)Appreciate it ;) |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 09:56:59
|
I had it the wrong way round - negtive level was children, positive level was parentsIt still should have gioven all the parent levels and children levels though.Don't really see the difference between what you have posted and one of the cte's I had. Yours won't give the parent hierarchy though.Are you sure you ran what was posted?;with cte as(select *, level = 0 from businessUnits where businessunitid = @businessunitidunion allselect b.*, level = level+1 from businessUnits b join cte on b.parentbusinessunitid = cte.businessunitid) ,cte1 as(select *, level = 0 from businessUnits where businessunitid = @businessunitidunion allselect b.*, level = level-1 from businessUnits b join cte1 on b.businessunitid = cte1.parentbusinessunitid)select * from cteunionselect * from cte1Note the entry for @businessunit is in both cte's with level 0.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Agenteusa
Starting Member
11 Posts |
Posted - 2011-07-07 : 10:10:00
|
Yes, I ran what you posted and the results were the unit which i received from the input, the ones one level above it and the ones one level below it.It didn´t return the children of the ones that were one level below it.Yes it won´t give the parents but I don´t need them since I only want the one the user inputs and its children. Basically the one I receive is the tarting point and it only goes down since its used as a filter. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 10:23:16
|
So you only want the tree starting from the entered value - that is a lot simpler than the question you asked ;with cte as(select *, level = 0 from businessUnits where businessunitid = @businessunitidunion allselect b.*, level = level+1 from businessUnits b join cte on b.parentbusinessunitid = cte.businessunitid) ,select * from cte==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Agenteusa
Starting Member
11 Posts |
Posted - 2011-07-07 : 11:15:21
|
Oh damn, I´m really sorry I forgot to update it on first post :SYes I only need the children of the one I receive as of now.sorry about that. |
|
|
|