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