Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    Reston, VA
    Posts
    59

    Unanswered: MySQL Tree Query

    Hello all,

    Just trying to represent a tree query in MySQL. I
    have a table setup called EMPLOYEE that has a
    employee to manager relation (manager IS an
    employee). How to extract this relation from MySQL?
    I read about a CONNECT BY PRIOR function in Oracle,
    but it does not appear MySQL has anything like it.

    Sample....

    EMPOYEE TABLE
    -EMP_ID
    -EMP_NAME
    -MGR_ID

    -CEO
    -VP ENGINEERING
    -DIR of SOFTWARE DEVELOPMENT
    -DEVELOPMENT MGR
    -DEVELOPER 1
    -DEVELOPER 2
    -QA MGR
    -QA 1
    -QA 2

    I think this illustrates the point. If I have a child
    entity, I have no problem going backwards, but I am
    stuck on how to write a query to list the whole
    relationship....? The top-down is giving me trouble. i want to be able to output.....

    /CEO
    /CEO/VP ENGINEERING
    /CEO/VP ENGINEERING/DIR of SOFTWARE
    DEVELOPMENT
    /CEO/VP ENGINEERING/DIR of SOFTWARE
    DEVELOPMENT/DEVELOPMENT MGR
    /CEO/VP ENGINEERING/DIR of SOFTWARE
    DEVELOPMENT/DEVELOPMENT MGR/DEVELOPER 1
    /CEO/VP ENGINEERING/DIR of SOFTWARE
    DEVELOPMENT/DEVELOPMENT MGR/DEVELOPER 2
    /CEO/VP ENGINEERING/DIR of SOFTWARE
    DEVELOPMENT/QA MGR
    /CEO/VP ENGINEERING/DIR of SOFTWARE
    DEVELOPMENT/QA MGR/QA 1
    /CEO/VP ENGINEERING/DIR of SOFTWARE
    DEVELOPMENT/QA MGR/QA 2

    this may seem confusing so let me know.......any help is greatly appreciated.....

    many thanks in advace!

  2. #2
    Join Date
    Jul 2003
    Posts
    35
    Hi,

    I have just been through the same exercise with MySQL. I would recommend you look at Joe Celko's (try searching Google as a starting point) work on Nested Sets and how he sets up hierarchies in relational databases. The Adjacency List method you are using will prove more hassle than it is worth when used with MySQL, as you will need some form of recursion to extract children etc. which MySQL doesn't support. And if you use recursion from the client side, you will end up accessing the database numerous times... which means SLOW. I ended up using Celko's stuff and while it does have limitations... for an employee hierarchy it worked well and i could get all children, an indented list, parents etc... all very efficiently too.

    Chrs,
    Ash

  3. #3
    Join Date
    Aug 2003
    Location
    Reston, VA
    Posts
    59
    Thanks Ash,

    I'll take a look. I did use the 'client workaround' which I hope does not bog down my box to a crawl. The sets of data are small, but since the 'nested set' could be several layers (there is currently no limit) many queries are necesary.

    Much appreciated! I'll let you know how it goes when I implement.

    Regards,
    Marty

Posting Permissions

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