Recursive Query or Procedure

asalamay · #1 (**permalink**) 10-03-07, 22:25

Hi I'm working on building a recursive query or procedure. I have a table that list the employees for the company. each row of data contains the employee ID and the Managers ID. I was making a query that did 11 joins from the Employee ID to the Managers ID. The hiearchy of the data is current 11 joins deep, but this could change and I would like to know if there is a dynamic way to create the joins the return the data. So I don't have to count how many joins are needed.

So what is the best way to handle this and if you post explain how the solution works.

gvee · #2 (**permalink**) 10-04-07, 03:46

Do you really need to show 11 levels of heirachy?

Personally I'd go with 3:
An employee, their manager(s) and the people who report into the employee.

stolze · #3 (**permalink**) 10-04-07, 07:00

You can simply use recursive queries:

Code:

WITH RECURSIVE hierarchy
   AS ( SELECT ...
        FROM   ...
        WHERE  ...
        UNION ALL
        SELECT ...
        FROM   ..., hierarchy
        WHERE  ... )
SELECT ...
FROM   hierarchy
WHERE  ...

Since SQL is a relational programming language, I wouldn't use stored procedures for such a task.

r937 · #4 (**permalink**) 10-04-07, 07:01

what kind of paleolithic organization has that many levels?

george's suggestion is one of the best for this type of problem

another is simply to code 17 levels of joins and give yourself some leeway

(and no, there is no difficulty in the database joining that many tables)

if neither of these suggestions satisfies, then you may wish to change your data model from the adjacency model (which is what the id/parentid model is called) to the nested set model