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