How to get a specific combination of columns in MySQL

Question

The table I'm working with is employees(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, MANAGER_ID, DEPARTMENT_ID)

The specific questions is Find all employees whose salaries are more than their managers, and display the salaries of those employees and the salaries of their managers.

I believe I've correctly found the employees who earn more than their managers(shown below), but I just can't seem to figure out how I would also display the managers salary.

select e.EMPLOYEE_ID, e.SALARY, MANAGER_ID
from employees e
where e.SALARY > (
    select e1.SALARY
    from employees e1
    where e.MANAGER_ID = e1.EMPLOYEE_ID
    )

This prints the employee ID, their salary, and the ID of the employees manager, so what I'm missing is that managers salary. Somehow I need to take the manager ID associated with the employee, and use that to find their salary, but I'm just stumped as to how I can do that in one query.

Ted Cohen Ted Cohen · Accepted Answer · 2014-02-23T21:08:18

Join the manager row to the employee row and you will have what you need.

select 
 e.employee_id, e.salary as emp_salary, e.manager_id, m.salary as mgr_salary
from employees as e
left outer join employees as m on m.employee_id=e.manager_id
where e.salary > m.salary

How to get a specific combination of columns in MySQL

1 Answers