Mysql Parent Child show parent after child

Question

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

Could you explain the logic? I can't understand it from what you want — Leandro Bardelli, Nov 15 '21 at 17:04
Maybe https://stackoverflow.com/questions/69741948/order-by-logic-for-notes-forum/69742830#69742830 will help? — Barmar, Nov 15 '21 at 17:07
I want show parent(green, second image) in row, now my result query just showing child in row(first image) — gdg tolis, Nov 15 '21 at 17:07
You should include your table structure here. As well as an explanation to the query your trying to achieve which will help us understand what type of results your hoping to get. — slashroot, Nov 15 '21 at 17:11
Duplicate of https://stackoverflow.com/questions/13382380/mysql-order-by-parent-and-child — Salman A, Nov 15 '21 at 17:41

score 0 · Answer 1 · answered Nov 15 '21 at 17:34

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...

score 0 · Accepted Answer · answered Nov 15 '21 at 17:54

0

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

answered Nov 15 '21 at 17:54

slashroot

773
1
4
13

score 0 · Answer 3 · answered Nov 15 '21 at 20:15

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

Mysql Parent Child show parent after child

3 Answers3