Mysql Parent Child show parent after child-CodePudding

I have table master_employee and fields employee_code, employee_name, cam_manager_code. my code:

SELECT 
    me.employee_name as employee_name,
    me_cam.employee_name as cam_manager_name, 
    me.employee_code
FROM master_employee as me_cam 
LEFT JOIN master_employee as me ON me.cam_manager_code = me_cam.employee_code AND me.deleted = 'N'
WHERE me.cam_manager_code = NULL 
ORDER BY cam_manager_name, employee_name;

my result:

I want it like below:

SIMPLE EXAMPLE

my table
id | name | parent_id
1  | a    | NULL
2  | a1   | 1
3  | a2   | 1
4  | b    | NULL
5  | b1   | 4
6  | b2   | 4 

result :
name | parent_name
a1   | a
a2   | a
a    | NULL
b1   | b
b2   | b 
b    | NULL

I want parent row after childs, example parent a after child a1 and a2

CodePudding user response：

Made a fiddle from your edit check it out

Using the example from the duplicate link posted by Salman A

In your ORDER BY use ORDER BY COALESCE(parent,id) ASC, parent DESC

CodePudding user response：

Select the childs,
do a union all to include the parents,
and add a column to do the correct ordering:

SELECT 
    me.employee_name as employee_name,
    me_cam.employee_name as cam_manager_name, 
    me.employee_code,
    1 as sortColumn
FROM master_employee as me_cam 
LEFT JOIN master_employee as me ON me.cam_manager_code = me_cam.employee_code AND me.deleted = 'N'
WHERE me.cam_manager_code = NULL 

UNION ALL

SELECT DISTINCT
    me_cam.employee_name ,
    me_cam.employee_name as cam_manager_name, 
    me.cam_manager_code,
    2 as sortColumn
FROM master_employee as me_cam 
LEFT JOIN master_employee as me ON me.cam_manager_code = me_cam.employee_code AND me.deleted = 'N'
WHERE me.cam_manager_code = NULL 

ORDER BY cam_manager_name, sortColumn, employee_name

and, yep, this is untested...

CodePudding user response：

Based on your description of master_employee table. I suggest this solution:

select me1.employee_name,me1.cam_manager_name, me1.employee_code 
from master_employee me1 inner join 
 (
   select employee_name, cam_manager_name, 
      min(employee_code) employee_code
   from master_employee
 group by employee_name
 ) me2
on me1.employee_name=me2.employee_name
and me1.employee_code <> me2.employee_code
union
(select employee_name, cam_manager_name, 
  min(employee_code) employee_code 
from master_employee
group by 2,1);

Try it with this fiddle link