Need T-SQL 2k5 help - please advise

Question

Need T-SQL 2k5 help - please advise

sql_jr

SSCommitted

Points: 1612
More actions
March 30, 2007 at 10:33 am

#171888

Basically I have a table where there are parents and children. I need a query to derive all the levels of children that roll-up to one parentID. So, the one parent can have many children, and those children can be parents of other children. So, if 2 is a parent of 7,8,9 and 7 is a parent of 3,4,5, then querying where parentID=2 should yield me 3,4,5,7,8,9. Below is a sample dataset. Hope this makes sense. Please advise. TIA
ApID ParentID
1    1
2    1
3    7
4    7
5    7
6    9
7    2
8    2
9    2
10    8
11    8

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

Do some research on Common Table Expressions (CTEs)... in this situation that's by far the best tool. In fact, I think I've seen an example exactly like this in a CTE article somewhere.

sql_jr SSCommitted Points: 1612 More actions · Answer 2

Thx Yes, I was considering a CTE, but came here for some help, specifically some code.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Yes, I know you want us to do your work for you.

N 56°04'39.16"
E 12°55'05.25"

sql_jr SSCommitted Points: 1612 More actions · Answer 4

Well, thanks Peter that was of great help. Is that not what these forums are for?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

I don't think so.

We can provide valuable help, and some insights, to point you in the right direction.

And that's what the others in this topic has done.

Now YOU have to show some improvement and post what you have come up with this far.

We can comment that and thus making YOU ultimately solving this problem yourself.

Doing this you will learn much much more than just copy and paste the solutions you want from us.

Please remember, you give from ourself without reimbursement!

We do this on our own free time because we like to help other gain knowledge about SQL Server.

And as Aaron wrote, with a simple SEARCH you can find the solution here at sqlservercentral forums!

But you have to put in some effort yourself.

N 56°04'39.16"
E 12°55'05.25"

CodeDigger Mr or Mrs. 500 Points: 506 More actions · Answer 6

The article on Common Table Expressions in Books Online is quite clear. Some guidelines: for a recursive CTE, you have to pay attention to 3 things:

You need an anchor member inside the CTE. That is the first SELECT in the WITH clause and it will generate the top-level set of rows. Depending on your starting conditions, you can encounter a top-down (the "ultimate bosses" are known-style) or a bottom-up ("the lowest-level workers have no subordinates"-style) recursive CTE.
In your case, it is a top-down CTE where you know the anchor parent id.
The tricky part is the recursive member of the CTE (the part after the UNION ALL). This statement will add ONE next level i+1 of rows by joining the database table(s) to a "temporary table" with the same name as the CTE, that contains all rows of level i.
If this is too abstract for you, work out the statement for the case i=0: the first iteration, where the CTE contains the "boss" records. Then check this statement for the case i=1: the second iteration, where the CTE contains the "sub-boss" records. 9 out of 10 times, it will be OK. In your case, it will be a join on <table>.ParentId = <cte>.ApId.
It is important that your CTE contains sufficient fields to make the join between the base tables and the results of the previous iteration. For technical reasons, you may have to add an field that is not used in the DML statement that follows it.
As a mind teaser: if you were to show only the ApId and the name of all items whose ultimate parent id is 2, do you need the ParentId field in the CTE?
This is often "automagically" taken care of, but you have to check it anyway: does your recursive CTE terminate? Is there a condition in the recursive member, so that it will return no rows if the CTE of the previous iteration contains the last level?

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 7

And the recursive section of BOL has an example doing almost exactly what is being requested here. You ought to be able to take the example & make it work. From BOL:

USE AdventureWorks;GOWITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)AS(-- Anchor member definition    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,         0 AS Level    FROM HumanResources.Employee AS e    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL    WHERE ManagerID IS NULL    UNION ALL-- Recursive member definition    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,        Level + 1    FROM HumanResources.Employee AS e    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL    INNER JOIN DirectReports AS d        ON e.ManagerID = d.EmployeeID)-- Statement that executes the CTESELECT ManagerID, EmployeeID, Title, LevelFROM DirectReportsINNER JOIN HumanResources.Department AS dp    ON DirectReports.DeptID = dp.DepartmentIDWHERE dp.GroupName = N'Research and Development' OR Level = 0;GOWhich returns the result:

ManagerID EmployeeID Title                                   Level--------- ---------- --------------------------------------- ------NULL      109        Chief Executive Officer                 0109       12         Vice President of Engineering           112        3          Engineering Manager                     23         4          Senior Tool Designer                    33         9          Design Engineer                         33         11         Design Engineer                         33         158        Research and Development Manager        33         263        Senior Tool Designer                    33         267        Senior Design Engineer                  33         270        Design Engineer                         3263       5          Tool Designer                           4263       265        Tool Designer                           4158       79         Research and Development Engineer       4158       114        Research and Development Engineer       4158       217        Research and Development Manager        4

Just filter for the data you need.

BOL is our bestest budy most of the time.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning