Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Recursive type SQL statement

    Good day,

    I have a database table, part of an exisitng application which cannot be changed I just have to use the table.
    The layout of the table is as follows:
    Manager Employee
    James Bob
    James Greg
    James John
    Bob Ross
    Bob Jason
    Greg Alex
    Ross Philip
    Ross Brian

    What I need to do is have a SQL statement return all the managers with their Employees and then those Employees who are managers with those that report to them, the Employee.

    Output needs to be something like this, Level is the level they are in in terms of where they are in reporting:
    Level Manager Employee
    1 James Bob
    1 James Greg
    1 James John
    2 Bob Ross
    2 Bob Jason
    2 Greg Alex
    3 Ross Philip
    3 Ross Brian

    I hope this makes sense.

    What I need is from one table that has both parents and children return those children of each parent where a child can be a parent and a parent can be a child and the level they are on in terms of reporting.

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What have you tried so far?
    Show us your SQL and any error message that you get

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is a common application of a CTE.
    Recursive Queries Using Common Table Expressions

    Rudy has an excellent article of more simple traversal of adjacency model hierarchies (that is what you have in case you didn't know the name) but I can never find it via google.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah here it is:Categories and Subcategories
    I had to include doggies in my search....

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    I had to include doggies in my search....
    perhaps you should simply remember "categories and subcategories" -- it's been #1 for years
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I shall do no such thing. It is also #1 for:
    "horsie" "doggie" "kittie"

    From this point hence that is how I shall google it.

Posting Permissions

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