CODEWITHSUNDEEP
mysqlsqldatabasehierarchyself-join
Anshul Parashar
Anshul Parashar

Reputation: 3126

Get all child hierarchy of a parent

I have a table employeehierarchy in which I have many records. I want to fetch all records in the hierarchy of senior.

In this table Anshul is senior most employees and I want to get all junior employee name which are under Anshul i.e Yogesh, Chirag, Vimal and Ajay. 

|   Id    |           name           |    | Senior     |    |  |  |  |  |
+---------+--------------------------+----+------------+----+--+--+--+--+
|      14 | Anshul                   |    |          0 |    |  |  |  |  |
|      15 | Yogesh                   |    |         14 |    |  |  |  |  |
|      16 | Chirag                   |    |         15 |    |  |  |  |  |
|      17 | Vimal                    |    |         16 |    |  |  |  |  |
|      18 | Ajay                     |    |         17 |    |  |  |  |  |

I need a mysql query for achieving this. Thanks

Upvotes: 1

Views: 687

Answers (2)

iTechOwl
iTechOwl

Reputation: 150

May this will help someone,

    SELECT  @id :=
        (SELECT  Id
        FROM    employeehierarchy
        WHERE   Senior = @id
        ) AS employee
FROM    (
        SELECT  @id := 14
        ) vars
STRAIGHT_JOIN
        employeehierarchy
WHERE   @id IS NOT NULL;

Upvotes: 1

codewizard
codewizard

Reputation: 1647

You would need something in your database to create a relationship between your sr and junior employee. Unfortunately its beyond the scope of this question on how you implement this relationship. As this touches more on database architecture. One option is to create a new field in your existing table which holds the id of your senior, for each employee. Another option would be to have a separate table where you have three fields id, employee id, senior id.

I suggest the second option. Then you can berform a conditional join in you SQL.

Upvotes: 0

Related Questions