TheFullMonty TheFullMonty - 6 months ago 8
SQL Question

Column Relationships

I realize i'm far off the solution with what i have:

Select FirstName || ' ' || LastName AS Manager From Employee
Where (Select COUNT(ReportsTo) from Employee
group by ReportsTo
order by ReportsTo desc);


ReportsTo
values are the
EmployeeID
they report to

What i want is to query the name of the employee with the most Employees reporting to them and who they in turn report to without nulls. I'm Not sure how to make the connections between columns values such as ReportsTo to EmployeeID so any explanation would help

For Example the output i would want is two columns say | Fred Jones | Mary Anne| the first being the employee with the most reportsTo with the same value as their EmployeeID and the second being the name of the employee with the same EmployeeID as the first employees ReportTo

Answer

Do this step by step:

First step: Count how many employees report to a person.

select reportsto, count(*) from employee group by reportsto;

We can order this result by count(*) and limit it to only get one row, so as to get the person with the most reporters. Only problem is: What to do in case of ties, i.e. two persons have the same highest amount of reporters? SQLite doesn't offer much to help here. We'll have to query twice:

select reportsto
from employee 
group by reportsto 
having count(*) =
(
  select count(*) 
  from employee 
  group by reportsto 
  order by count(*) desc
  limit 1
);

Next step: Get the name. That means we must access the table again.

select 
  firstname || ' ' || lastname as manager
from employee
where e1.employeeid in
(
  select reportsto
  from employee 
  group by reportsto 
  having count(*) =
  (
    select count(*) 
    from employee 
    group by reportsto 
    order by count(*) desc
    limit 1
  )
);

Last step: Get the persons our found managers themselves report to. These can be many, so we group by manager and concatenate all those they report to.

select 
  e1.firstname || ' ' || e1.lastname as manager,
  group_concat(e2.firstname || ' ' || e2.lastname) as reportsto
from employee e1
join employee e2 on e2.employeeid = e1.reportsto
where e1.employeeid in
(
  select reportsto
  from employee 
  group by reportsto 
  having count(*) =
  (
    select count(*) 
    from employee 
    group by reportsto 
    order by count(*) desc
    limit 1
  )
)
group by e1.firstname || ' ' || e1.lastname;