Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 I have a problem recursive related

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...

Corey

I Has Returned!!
Go to Top of Page

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 :S

USE AdventureWorks2008R2;
GO
WITH 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 DirectReports
ORDER BY ManagerID;
GO

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 06:00:00
see
http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-common-table-expressions/

Actually what you have looks good - does it work?

==========================================
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.
Go to Top of Page

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.
Go to Top of Page

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 like

with cte as
(
select *, level = 0 from businessUnits where businessunitid = @businessunitid
union all
select b.*, level = level+1 from cte join businessUnits b on b.businessunitid = cte.parentbusinessunitid
) ,
cte1 as
(
select *, level = 0 from businessUnits where businessunitid = @businessunitid
union all
select b.*, level = level-1 from cte join businessUnits b on cte.businessunitid = b.parentbusinessunitid
)
select * from cte.*
union
select * 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.
Go to Top of Page

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.*
union
select * 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.
Go to Top of Page

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 wrong

like:


;with cte as
(
select *, level = 0 from businessUnits where businessunitid = @businessunitid
union all
select b.*, level = level+1 from businessUnits b join cte on b.businessunitid = cte.parentbusinessunitid
) ,
cte1 as
(
select *, level = 0 from businessUnits where businessunitid = @businessunitid
union all
select b.*, level = level-1 from businessUnits b join cte1 on cte1.businessunitid = b.parentbusinessunitid
)
select * from cte.*
union
select * from cte1.*


Corey

I Has Returned!!
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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 = @businessunit


Might help someone.

Thanks for your replies ;)

Appreciate it ;)
Go to Top of Page

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 parents
It 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 = @businessunitid
union all
select b.*, level = level+1 from businessUnits b join cte on b.parentbusinessunitid = cte.businessunitid
) ,
cte1 as
(
select *, level = 0 from businessUnits where businessunitid = @businessunitid
union all
select b.*, level = level-1 from businessUnits b join cte1 on b.businessunitid = cte1.parentbusinessunitid
)
select * from cte
union
select * from cte1

Note 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.
Go to Top of Page

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.
Go to Top of Page

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 = @businessunitid
union all
select 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.
Go to Top of Page

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 :S

Yes I only need the children of the one I receive as of now.

sorry about that.
Go to Top of Page
   

- Advertisement -