0

ManagerId is nothing but EmpId. i need all the EmpId that come under the given EmpId including all the subtree. without using CTE as i'm trying this with HQL. with no hierarchy level defined.

+-------+-----------+
| EmpId | ManagerId |
+-------+-----------+
|     1 |      null |
|     2 |         1 |
|     3 |         2 |
|     4 |         3 |
|     5 |         1 |
|     6 |         3 |
|     7 |         6 |
|     8 |         6 |
|     9 |      null |
|    10 |         3 |
|    11 |        10 |
|    12 |         1 |
|    13 |        12 |
+-------+-----------+

when the givem EmpId is 3: expected response:

4
6
10
7
8
11
BlueMoon
  • 33
  • 4
  • Possible duplicate of https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – Bill Karwin May 09 '19 at 20:03
  • Possible duplicate of https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree – Bill Karwin May 09 '19 at 20:03
  • Without CTEs or a pre-defined depth limit, you have to loop to deal with this kind of hierarchy. – Uueerdo May 09 '19 at 20:08
  • is there any other possibility with mySQL 5.7.22? – BlueMoon May 09 '19 at 20:22
  • If you don't want to loop on the client side, you could make a stored proc in MySQL that could do the looping instead; but without knowing the depth ahead of time, there is no non-CTE query of a table like this that can be executed once to get all levels. – Uueerdo May 10 '19 at 19:10

0 Answers0