Select data according to manager & employee level in mysql

Question

Table structure is like this -

id Name Manager 1 aa NULL 2 hh 1 3 YY 4 4 Kk NULL 5 PP 4

So I want the result like-

Name Manager aa NULL hh aa Kk NULL YY Kk PP Kk

Answer 1

Try this:

SELECT b.name, a.name FROM table AS a RIGHT JOIN table AS b ON a.manager = b.id

Answer 2

UNTESTED Try this:

SELECT Name FROM table ORDER BY IFNULL(Manager,id), IFNULL(Manager,0), Name

I think this would get you all the managers grouped together, with the managers being in front of their employees who should be sorted by name. Unfortunately I can't get to a MySQL server instance at the moment to test.

Answer 3

SELECT a.name AS employee, b.name AS manager FROM employees AS a JOIN employees AS b ON a.manager_id = b.id

This would work , will give you employee and its manager