sql - I need to write a query to list all employees and the number of subordinates they have -

employees no subordinates should listed having 0 subordinates should displayed query. can return employees subordinates, can't seem display employees 0 subordinates.

here code far:

select s.empno, s.ename, count(*) "num_subordinates" emp e join emp s on s.empno=e.super  group s.empno, s.ename; 

the missing in statement use left join rather inner join. need specify id instead of * when counting in left join not yield 1 on count() if employee don't have subordinate.

select e.empno, e.ename, count(s.empno) "num_subordinates"   emp e        left join emp s on s.empno = e.super  group  e.empno, e.ename 

to further gain more knowledge joins, kindly visit link below:

• visual representation of sql joins