Recursive Queries Manager/Employee HELP!!

[carmo]

Wow, I need help with this! I have a table structure similar to the one below where an employee has a manager and a employee can also be a manager. I need to create a query that displays all employees directly and indirectly that report to a specified manager:

EmployeeID | ManagerID
-----------------------
101 110
102 110
103 110
104 103
105 103
106 103

I want to say "Give me all employee's that report to manager 110".....This should include the employees that directly report to 110 and the employees that report to 103.

Thanks In Advance
Carmo

 

[Golom]

This does it for you for the two level nesting that you have. If you are nested deeper you will need to add more UNIONs.

SELECT E.ManagerID As [Manager], E.EmployeeID As [Employee]
FROM EmployeeManager AS E
WHERE E.ManagerID = 110

UNION

SELECT F.ManagerID As [Manager], F.EmployeeID As [Employee]
FROM EmployeeManager AS E INNER JOIN EmployeeManager AS F ON F.ManagerID = E.EmployeeID
WHERE E.ManagerID = 110

 

[carmo]

What if you don't know before hand how deep the hierarchy goes. It could be different for every manager.

Thank you Golom

 

[Golom]

You can code as many UNION clauses as you want (i.e. the maximum level of nesting that you expect). The lower level queries will simply not return any records.

 

[carmo]

Thanks, I tried the query....but does this work even if you don't know what the manager's IDs will be. I can't place the manager's IDs in each of the where clauses because I don't know what they will be in advance. Thanks.

 

[Golom]

You can parameterize the query with something like:

PARAMETERS MGR_ID Long;
<Current SQL>

and change the SQL where the &quot;ManagerID = 110&quot; in the example appears to &quot;ManagerID = [MGR_ID]&quot;. Depending on how you are running it (directly as a query or from code) the query will pop-up a box asking for a value for MGR_ID each time it runs and will plug the response into each place that it is referenced.

 

[carmo]

I understand that....I will be doing it through code. But here is my sample table:

empID empName manID manName
99 Joe 100 Sammy
100 Sammy 201 Carla
101 Peter 201 Carla
102 Natalie 201 Carla
103 Smith 201 Carla
201 Carla 301 Luc
202 Denis 301 Luc
203 Norman 301 Luc


And here is the query you showed me:

SELECT E.manID, E.empID
FROM employee AS E
WHERE E.manID = '301' ;

UNION

SELECT F.manID, F.empID
FROM employee AS E INNER JOIN employee AS F ON F.manID = E.empID
WHERE E.manID = '301'

UNION

SELECT F.manID, F.empID
FROM employee AS E INNER JOIN employee AS F ON F.manID = E.empID
WHERE E.manID = '301';



Although, I am only getting two levels. I am not receiving the 3rd level. Do you know what I am doing wrong. Also, if you have a chance, could you briefly explain the logic behind your union query. Thanks....greatly appreciated.

 

[carmo]

With the query I created, it seems like I have to supply the manager's ID at each level. I would like to supply only one ID (ex: 301), and the query will give me all the levels. Thanks.

 

[Golom]

The SQL for three levels is

SELECT E.ManagerID As [Manager], E.EmployeeID As [Employee]
FROM EmployeeManager AS E
WHERE E.ManagerID = 110

UNION

SELECT F.ManagerID As [Manager], F.EmployeeID As [Employee]
FROM EmployeeManager AS E INNER JOIN EmployeeManager AS F ON F.ManagerID = E.EmployeeID
WHERE E.ManagerID = 110

UNION

SELECT F.ManagerID As [Manager], F.EmployeeID As [Employee]
FROM EmployeeManager AS E INNER JOIN EmployeeManager AS F ON F.ManagerID = E.EmployeeID
WHERE E.ManagerID IN (SELECT F.ManagerID As [Manager]
FROM EmployeeManager AS E INNER JOIN EmployeeManager AS F ON F.ManagerID = E.EmployeeID
WHERE E.ManagerID = 110)


Explain it ... OK ... I'll try.

SELECT E.ManagerID As [Manager], E.EmployeeID As [Employee]
FROM EmployeeManager AS E
WHERE E.ManagerID = 110

... retrieves all the employees who report directly to manager 110.

SELECT F.ManagerID As [Manager], F.EmployeeID As [Employee]
FROM EmployeeManager AS E INNER JOIN EmployeeManager AS F ON F.ManagerID = E.EmployeeID
WHERE E.ManagerID = 110

... finds all the employees that report to someone who reports to manager 110 (i.e. second level)

SELECT F.ManagerID As [Manager], F.EmployeeID As [Employee]
FROM EmployeeManager AS E INNER JOIN EmployeeManager AS F ON F.ManagerID = E.EmployeeID
WHERE E.ManagerID IN (SELECT F.ManagerID As [Manager]
FROM EmployeeManager AS E INNER JOIN EmployeeManager AS F ON F.ManagerID = E.EmployeeID
WHERE E.ManagerID = 110)

... is the third level of reporting. Everybody that reports to someone, who reports to someone, who reports to manager 110.

The forth level (are you ready for this?) is

SELECT F.ManagerID As [Manager], F.EmployeeID As [Employee]
FROM EmployeeManager AS E INNER JOIN EmployeeManager AS F ON F.ManagerID = E.EmployeeID
WHERE E.ManagerID IN
(SELECT F.ManagerID As [Manager]
FROM EmployeeManager AS E INNER JOIN EmployeeManager AS F ON F.ManagerID = E.EmployeeID
WHERE E.ManagerID IN (SELECT F.ManagerID As [Manager]
FROM EmployeeManager AS E INNER JOIN EmployeeManager AS F ON F.ManagerID = E.EmployeeID
WHERE E.ManagerID = 110))


In essence, each new level takes the SQL from the preceding level; removes the EmployeeID field and uses that whole expression in the &quot;WHERE E.ManagerID IN &quot; clause. If I really think about it there are probably more concise and elegant ways to do it.

BTW: A friend of mine once said that women are elegant; code is merely efficient ... oh well ...

 

[carmo]

Golom, Thanks a million. I appreciate all the help. I will try it out and let you know how it goes. I know there are other ways of doing this using VBA (or PL/SQL, Prolog)...but I was sure that there was a creative way to do it with plain SQL. Thanks Again.

 

[Golom]

Creative? ... Don't know about that ... I'd classify this as more brute force ... but if it works for you ... hey what the h***!

 

[carmo]

Golom, I tried it....it seems to work, but I could see that this is a quick and dirty approach. I would like to do this in VBA using the same logic. I think I would need to use a recursive function....I am just not sure how to get started. Could you provide me with a hand. Thanks Golom....you have know idea how much I appreciate your help.

 

[Golom]

If I were writing this in VBA or VB using recursion, I'd probably do something like this:

Sub cmdShow110_Click()
Dim nH as Integer
nH = FreeFile
Open &quot;Heirarchy.Txt&quot; For Output As #nH
Print #nH, &quot;Reporting Heirarchy For 110&quot;
Print #nH,

ReportHeirarchy 110, nH, 0

Close #nH
End Sub


Sub ReportHeirarchy (ManagerID as Long,
nH As Integer,
Optional Indent As Integer = 0)
Dim db As DAO.DataBase
Dim rs As DAO.Recordset

Set db = Currentdb()
Set rs = db.OpenRecordset ( _
&quot;SELECT E.EmployeeID &quot; & _
&quot;FROM EmployeeManager AS E &quot; & _
&quot;WHERE E.ManagerID = &quot; & ManagerID

Do Until rs.EOF
Print #nh, Space(Indent) & rs![EmployeeID]
ReportHeirarchy rs![EmployeeID], nH, Indent + 3
rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing

End Sub

I haven't tested any of this but that's the general idea. This just dumps it to a text file. You could populate a database table, display it on a form, etc.

 

[carmo]

This is pretty fustrating....I don't know how I am going to get this to work. What I eventually want to do is create a form with a drop down list of managers IDs. The user selects a manager ID, and it will pass all the children records to a query that is used as a source to a report. Thanks for the response Golom.

 

[Golom]

Frustration is not necessary ... but if it feels appropriate then do it.

What I gave you is a solution that dumps the result to a print file. Recast your thinking. Suppose, in the calling routine you were to create a new table and then in the ReportHeirarchy routine you were to append records to that table instead of printing them. Sounds like exactly the solution you want. All you need do then is write a query that retrieves the contents of the table and you're done.

 

[gol4]

Here is an article that discusses another option

http://www.sqlteam.com/item.asp?ItemID=8866