select t.dept_no,t.emp_no,(select MAX(salary) as maxSalary from (SELECT de.dept_no,s.emp_no,s.salary from salaries s LEFT JOIN dept_emp de on s.emp_no=de.emp_no) s where s.dept_no=t.dept_no ) as maxSalary from(SELECT de.dept_no,s.emp_no,s.salary from salaries s LEFT JOIN dept_emp de on s.emp_no=de.emp_no) t GROUP BY dept_no