How to write a query with workers and their managers beside them in on query-CodePudding

I have 2 tables (in the screenshot tables). One table employees with also workers and also managers and the other (departments) with the departments. And I need to write a query that returns the names of the employees, the name of their direct manager, the name of their department and their salary plus a sales commission (COMM). The empty cells in the screenshot mean NULL.also, to some workers, they don't have managers!

I tried to do query with Union all, but it's a problem because the rows are different!. So how should I write it?

select e.ename, d.name, sum (case when e.comm<>null then 1 else 0  case when e.sal<>null then 1 else 0 end)as sum_total 
from emp as e 
left join dept as d on e.deptno=d.deptno
where e.job<>"Manager"

Union ALL

select e.ename
from emp as e
left join dept as d on e.deptno=d.deptno
where e.job="Manager"

Thank you in advance

CodePudding user response：

You can self join the emp table to get the managers name

select e.ename, 
       d.name, 
       coalesce(e.sal, 0)   coalesce(e.comm, 0) as salary, 
       m.ename as manager
from emp e 
join dept as d on e.deptno = d.deptno
left join emp m on m.empno = e.mgr