CODEWITHSUNDEEP
mysqlgroup-by
Jane Wilkie
Jane Wilkie

Reputation: 1743

Grouping results in MySQL

My brain is turning into tapioca trying to figure this one out. I have a table of subordinates and supervisors. Every employee has a number of course. Here's an example.

I have three fields: employee_id, name, supervisor_id

Fred Wilkie is a supervisor and his record is ....

employee_id: 1
name: Fred Wilkie
supervisor_id: NULL

Ted Wilkie is a lowly worker and Fred is his boss. His entry looks like this....

employee_id: 2
name: Ted Wilkie
supervisor_id: 1

What I would like my query to look like is employee_id, name and supervisor_id but the supervisor_id should be equal to the employee_id if the supervisor_id is NULL.

This kinda works... ( I think I just need to refine it somehow)

select employee_id, name, supervisor_id case when supervisor_id is NULL then employee_id else supervisor_id end from employees order by supervisor_id;

Problem with this is that it orders all of the records first where the employee_id is equal to the supervisor_id and then it just spits out the subordinates that are left.... 

employee_id, name, supervisor_id
1, Fred Wilkie, 1
4, Gail Winston, 4
2, Ted Wilkie, 1
3, Lisa Wilkie, 1
5, Russ Cablas, 4
6, Ben Reynolds, 4
etc, etc, etc...

what I would like is this......

employee_id, name, supervisor_id
1, Fred Wilkie, 1
2, Ted Wilkie, 1
3, Lisa Wilkie, 1
4, Gail Winston, 4
5, Russ Cablas, 4
6, Ben Reynolds, 4
etc, etc, etc...

In the example above I have the the first supervisor (Fred) listed (employee_id = supervisor_id) and then all of his subordinates. After that is Gail, and all of her subordinates, etc. I think my group-fu is weak.

We run a large company (250 employees) so we'd like a way to keep this in the MySQL logic. Does anyone have an idea?

Many thanks! Janie

Upvotes: 1

Views: 93

Answers (3)

John Woo
John Woo

Reputation: 263933

The easiest solution on this is to use COALESCE

SELECT  employee_ID,
        name,
        COALESCE(supervisor_id, employee_id) AS supervisor_id
FROM    employees
ORDER BY supervisor_id, employee_ID

SQLFiddle Demo

additional query (if you want to get their supervisor name instead of id)

SELECT  a.employee_ID,
        a.name,
        COALESCE(b.name, a.name) AS Supervisor_Name
FROM    employees a
        LEFT JOIN employees b
          ON a.supervisor_ID = b.employee_ID
ORDER BY COALESCE(b.supervisor_id, a.employee_id), employee_ID

SQLFiddle Demo

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

i think you are missing the sequence to get records you are using order by but not ponting the sequence ASC or DESC

select employee_id, name, 
supervisor_id case when supervisor_id is NULL 
then employee_id else supervisor_id end 
from employees 
order by supervisor_id
ASC;

hope this works

Upvotes: 0

Kokozaurus
Kokozaurus

Reputation: 639

select employee_id, name, supervisor_id case when supervisor_id is NULL then employee_id else supervisor_id end from employees order by supervisor_id ASC;

should do the trick...

Upvotes: 0

Related Questions