mysqlcorrelated-subqueryin-subquery

Get field from MYSQL subquery from outer main query


In my mysql db I have an employee table (EMP) and a department table (DEPT) and want to find employees with highest salary in each department.

So first I grouped the departments like this

SELECT
    dept.DNAME as department,
    MAX(emp.SAL) as maximum_sal
FROM
    `EMP` emp
    JOIN `DEPT` dept ON emp.DEPTNO = dept.DEPTNO
GROUP BY
    dept.DNAME
ORDER BY
    maximum_sal DESC;

DB Results from grouping

Now to get the employees associated with these departments I have tried doing this:

SELECT
    emp.ENAME,
 emp.SAL,
    department
FROM
    `EMP` emp
WHERE
    (department, maximum_sal) IN (
        SELECT
            dept.DNAME as department,
            MAX(emp.SAL) as maximum_sal
        FROM
            `EMP` emp
            JOIN `DEPT` dept ON emp.DEPTNO = dept.DEPTNO
        GROUP BY
            dept.DNAME
        ORDER BY
            maximum_sal DESC
    );

But I get the error Unknown column 'department' in 'field list'

What am I doing wrong? How do I get fields from the inner subquery to show up in my select outer query.


Solution

  • No matter the MySQL version you can do this:

    SELECT emp.ENAME,
           emp.SAL,
           dept.dname
    FROM `EMP` emp
    JOIN `DEPT` dept ON emp.DEPTNO = dept.DEPTNO
    JOIN
    (
      SELECT DEPTNO, max(sal) as msal
      FROM emp
      GROUP BY DEPTNO
    ) tmp ON tmp.DEPTNO = emp.DEPTNO 
         AND tmp.msal = emp.sal
    GROUP BY dept.DNAME
    

    SQLFiddle demo

    Your query won't work since your "subquery" is only part of an IN clause which you cannot reference from outside.