Problem
Table: Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| salary | int |
| managerId | int |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.
Write an SQL query to find the employees who earn more than their managers.
Examples
Example 1:
Input:
Employee table:
+----+-------+--------+-----------+
| id | name | salary | managerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | Null |
| 4 | Max | 90000 | Null |
+----+-------+--------+-----------+
Output:
+----------+
| Employee |
+----------+
| Joe |
+----------+
Explanation:
Joe is the only employee who earns more than his manager.
Accepted
Solution
Method 1 - Self join
SELECT em.name AS Employee
FROM Employee em
JOIN Employee ma ON ma.id = em.managerId
WHERE ma.salary < em.salary;
Note that we are doing self join and also we have to use em.name
otherwise it will crib about ambigous name
column. We also alias name to employee to meet the output requirements.