Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    22

    Unanswered: Recursive Query or Procedure

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by r937
    what kind of paleolithic organization has that many levels?
    Government and military. Hell, military has a *dual* hierarchy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •