SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 I have a problem recursive related
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Agenteusa
Starting Member

10 Posts

Posted - 07/06/2011 :  14:42:24  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/06/2011 :  15:11:44  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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

10 Posts

Posted - 07/07/2011 :  05:51:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3355 Posts

Posted - 07/07/2011 :  06:00:00  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 07/07/2011 06:01:00
Go to Top of Page

Agenteusa
Starting Member

10 Posts

Posted - 07/07/2011 :  06:12:13  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

United Kingdom
3355 Posts

Posted - 07/07/2011 :  06:19:52  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

10 Posts

Posted - 07/07/2011 :  06:37:51  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/07/2011 :  07:21:02  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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

10 Posts

Posted - 07/07/2011 :  09:27:33  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

United Kingdom
3355 Posts

Posted - 07/07/2011 :  09:31:06  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

10 Posts

Posted - 07/07/2011 :  09:44:38  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

United Kingdom
3355 Posts

Posted - 07/07/2011 :  09:56:59  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 07/07/2011 10:23:36
Go to Top of Page

Agenteusa
Starting Member

10 Posts

Posted - 07/07/2011 :  10:10:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3355 Posts

Posted - 07/07/2011 :  10:23:16  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 07/07/2011 10:25:34
Go to Top of Page

Agenteusa
Starting Member

10 Posts

Posted - 07/07/2011 :  11:15:21  Show Profile  Reply with Quote

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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000