Home > Software engineering >  How to write a query with workers and their managers beside them in on query
How to write a query with workers and their managers beside them in on query

Time:10-27

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
  • Related