how can i use LIMIT operator in subquery with IN operator in outer query in MySQL

Question

my query is like

SELECT e.emp_name, d.dept_name, s.salary
FROM employes e
JOIN departments d
ON e.dept_id = d.dept_id
JOIN salary s
ON e.emp_id = s.emp_id
AND e.emp_id NOT IN (SELECT emp_id
    FROM salary s
    ORDER BY salary DESC
    LIMIT 2)
ORDER BY salary DESC
LIMIT 1

and i am getting this error...

[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Answer 1

You can switch to a left outer join:

SELECT e.emp_name, d.dept_name, s.salary
FROM employes e JOIN
     departments d
     ON e.dept_id = d.dept_id JOIN
     salary s
     ON e.emp_id = s.emp_id LEFT OUTER JOIN
     (SELECT emp_id
      FROM salary s
      ORDER BY salary DESC
      LIMIT 2
     ) s2
     on e.emp_id = s2.emp_id
WHERE s2.emp_id IS NULL
ORDER BY salary DESC
LIMIT 1;