0

I think my problem can be solved with a 'recursive query', but since MySQL doesn't support recursive queries could some suggest me as to how to go about with this?.

Here's an example of what I need to do:

Input Table Employee:

Role | Parent_Role| Person    | Geo| Region |District
----------------------------- -----------------------

Rep-1   DM-1    Lou Gertsner    AME West    CA

Rep-2   DM-1    Steve Ballmer   AME West    CA

DM-1    RVP-1   Marc Benioff    AME West    CA

RVP-1   GEO-1   Ray Ozzie   AME West    Null

WW      Null    Larry Ellison   AME Null    Null

GEO-1   WW  Bill Gates  AME Null    Null

Rep-3   DM-1    Vinod Khosla    AME West    CA

Rep-4   DM-1    Marc Benioff    AME West    CA

Output Table

WW_Employee |   Geo_Employee    | Region_Employee | District_Employee|Role  Employee    Geo |   Region |    District |
--------------------------- ----------------------- ------------------------------------

Larry Ellison   Bill Gates  Ray Ozzie   Marc Benioff    Rep-1   Lou Gertsner    AME West    CA

Larry Ellison   Bill Gates  Ray Ozzie   Marc Benioff    Rep-2   Steve Ballmer   AME West    CA

Larry Ellison   Bill Gates  Ray Ozzie   Marc Benioff    DM-1    Marc Benioff    AME West    CA

Larry Ellison   Bill Gates  Ray Ozzie   Null    RVP-1   Ray Ozzie   AME West    Null    

Larry Ellison   Null    Null    Null    WW  Larry Ellison   Null    Null    Null    

Larry Ellison   Bill Gates  Null    Null    GEO-1   Bill Gates  AME Null    Null    

Larry Ellison   Bill Gates  Ray Ozzie   Marc Benioff    Rep-3   Vinod Khosla    AME West    CA  

Larry Ellison   Bill Gates  Ray Ozzie   Marc Benioff    Rep-4   Marc Benioff    AME West    CA

Using the Input Table , I want to create a role table(Output Table) that lists (in separate columns by role type) the managers in the hierarchy for every employee. The role is the unique key and the parent role defines the hierarchy in the input table. I'll appreciate your help in this!

user2512372
  • 1
  • 3
  • It's not at all clear to me what you want to achieve here. You've shown us the table you're starting from and the output you want, but without some explanation I don't understand what the latter is meant to represent – James Green Jun 22 '13 at 22:11
  • 1
    I have now edited the question and I hope it is clear to you now. – user2512372 Jun 22 '13 at 22:15
  • 1
    I think I see where you're going. What you're looking for seems similar to the requirements in http://stackoverflow.com/questions/3438111/mysql-stored-procedure-that-calles-itself-recursively -- the answers there might give you some ideas to try out? – James Green Jun 22 '13 at 22:26
  • Possible duplicate of the aforementioned question. Other must-read questions on this topic: [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree), [What are the Options for Storing Hierarchical Data in a Relational Database?](http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) – RandomSeed Jun 23 '13 at 00:49

0 Answers0