How do hierarchy SELECT statement...

RYU

Hi, I have a table called tblUserDetail which contains the following attributes: - ID - ManagerID (which will refer to ID) - etc... Each user can have only 1 manager, but each user can have 0 or more staff. Now, I have the following structure

  A                        G

---|--- ---|---
| | | | |
B C D H I
|
E
|
F

Therefore, if I choose person "A" (for example), it will allow me to add person "G" as person "A" staff. If I choose person "F" it will also allow me to add person "G" as person "F" staff. Now, I can handle 3 level of hierarchy, but I don't know how to do recursive in SQL statement. Here is my SQL Statement: eg. thisUserID = [current selected user] thisUserManagerID = [the selected manager id]

strSQL = "SELECT UserID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE " +
"UserID<>" + thisUserID.ToString() + " AND ManagerID<>" + thisUserID.ToString() + " AND " +
"UserID<>" + thisUserManagerID.ToString() + " AND ManagerID=-1 AND " +
"(NOT EXISTS(" +
"SELECT * FROM tblUserDetail u2 WHERE u2.UserID=" + thisUserManagerID.ToString() + " AND " +
"NOT EXISTS(" +
"SELECT * FROM tblUserDetail u3 WHERE u1.UserID<>u2.ManagerID" +
")" +
")" +
")";

Currently, if I choose person "F" it will allow me to add "A" and "G" as person "F" staff because it doesn't handle more than 3 level of hierarchy. Now, this works perfectly for 3 level of hierarchy, but I need more than 3 level. Is there a way to do recursive statement within SQL statement? Cheers :)

Syed Mehroz Alam

SQL Server 2005 provides common table expressions (CTE) for recursion. You can view more here[^] Regards, Mehroz

RYU

Hi Syed, I am having a trouble to bind between fields. Here is a simple example

WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.UserID<>u1.ManagerID
)

SELECT * FROM NewUserDetail

And I got this message:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "u1.ManagerID" could not be bound.

I need to bind value to recursively go through all the users so the user will not be able to select its manager or manager of its manager. Any idea on how to tackle this problem? Cheers :)

Syed Mehroz Alam

Seems you didnt get the cte syntax right. The second query inside the cte syntax generally joins with the cte itself, not the first query. Lets have a cte that lists your user and all its subordinates

WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.ManagerID=NewUserDetail.UserID
)

SELECT * FROM NewUserDetail

Khawar Abbas1

The problem resides in your recursive call. The line is "SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 WHERE u2.ManagerID=NewUserDetail.UserID" Here you have to add a inner join to the NewUserDetail table. I am also giving you an example. Hopefully it will solve your problem. The example is given below: WITH TemplateTopic(parent_id, child_id, child_name, child_type, Level)AS ( -- Anchor member definition SELECT a.parent_id,a.child_id, a.child_name, a.child_type, 0 AS Level FROM tbltemplate_topics a Where Child_Id = 16 UNION ALL ---- Recursive member definition SELECT a.parent_id,a.child_id, a.child_name, a.child_type, Level + 1 AS Level FROM tbltemplate_topics a INNER JOIN TemplateTopic AS d ON a.parent_id = d.child_id ) -- Statement that executes the CTE SELECT * FROM TemplateTopic (Make changes in your code accordingly)

Do good and have good.

Syed Mehroz Alam

Oh...thanks for pointing. I missed the inner join part, dont know why.. Here is the modified cte

WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u2 Inner Join NewUserDetail Manager WHERE u2.ManagerID=Manager.UserID
)

SELECT * FROM NewUserDetail

RYU

Hi Syed, I tried your example and it gave me this error

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'WHERE'.

Any idea? Thanks again :)

RYU

Hi Syed, Nevermind, I figured out the syntax with the help of the example given by Silent Eagle (thanks Silent Eagle). It should read:

WITH NewUserDetail (UserID, ManagerID, FullName)
AS
(
SELECT UserID, ManagerID, FirstName + ' ' + LastName As [FullName] FROM tblUserDetail u1 WHERE u1.UserID=12
UNION ALL
SELECT u2.UserID, u2.ManagerID, u2.FirstName + ' ' + u2.LastName As [FullName] FROM tblUserDetail u2 Inner Join NewUserDetail AS Manager ON u2.UserID<>Manager.ManagerID
)

But it again gave me another error on execution.

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

That is wierd because I did re-check and make sure there is no recursive employee-manager relation. Any idea? Thanks again :)

RYU

I see what is the problem. I shouldn't put

u2.UserID<>Manager.ManagerID

instead I should put

u2.UserID=Manager.ManagerID

Thanks Syed and Silent Eagle for your help. Cheers :)