Given a list of employees and their corresponding managers, we would like to find those customers who earn less than their managers.
| CustomerID | Name | Salary | Manager |
| 1 | Dave | 1000 | 2 |
| 2 | Peter | 2000 | |
| 3 | Salem | 3000 | 2 |
| 4 | Doe | 5000 | 2 |
In this example, we can see that Peter is the manager of three customers (Dave, Salem and Doe). But only Salem and Doe earn more than him.
Solution.
In this kind of situation, we can see that the manger is also an employee and it is stored in the same employee table. Therefore, we can have a self join here and check if a certain employee e make more money than his manager m.
select e.Name Employee
from Employee e , Employee m
where e.Manager = m.Id and
e.salary > m.Salary and
e.Manager is not null