An SQL query for all people managed by an employee

How to design a table to store all employee information of a company, employee information when there is a subordinate relationship, how can quickly find a staff management of all personnel?

Tree

CEO 1

|--manage1 11

|-------Leader1 111

| |-------employee1 1111

| |-------employee2 1112

|

|-------Leader2 112

|-------employee3 1121

|-------employee4 1122

...

Table_employees

ID Name parent_id employee_id

1 CEO NULL 1

2 Manager1 1 11

3 Leader1 2 111

4 Employee1 3 1111

5 Employee2 3 1112

6 Leader2 2 112

7 Employee3 6 1121

8 Employee4 6 1122

.....

SELECT * from table_employees WHERE parent_id = 2;

+----+---------+-----------+

| ID | name | parent_id |

+----+---------+-----------+

| 3 |         Leader1 | 2 |

| 6 |         Leader2 | 2 |

+----+---------+-----------+

Obviously not, because the employee information is missing

A) think of a way to use the algorithm, add a employee_id field, including all the parent node's employee_id information, when the lookup is calculated directly.

ALTER TABLE table_employees ADD COLUMN employee_id Int (4) not NULL DEFAULT 0;

Query all employees under Manage1:

SELECT * from Table_employees WHERE employee_id like ' 11% ' and ID <> 2;

+----+-----------+-----------+-------------+

| ID | name | parent_id | employee_id |

+----+-----------+-----------+-------------+

| 3 |         Leader1 |         2 | 111 |

| 4 |         Employee1 |        3 | 1111 |

| 5 |         Employee2 |        3 | 1112 |

| 6 |         Leader2 |         2 | 112 |

| 7 |         employee3 |        6 | 1121 |

| 8 |         employee4 |        6 | 1122 |

+----+-----------+-----------+-------------+

This method can, but need to design the table and insert data when the good employee_id, for the old system, do not have this field how to deal with it?

b) Link Query

SELECT t1.* from Table_employees T1 INNER joins table_employees t2 on t2.id = t1.parent_id WHERE t1.parent_id = 2 UNION ...

Estimated use of stored procedures ...

An SQL query for all people managed by an employee