( Oregon | 2020. 10. 12., h – 21:50 )

SELECT e.*, 
(SELECT MAX(`from_date`) FROM `titles` WHERE titles.emp_no = e.emp_no) AS beosztas,
(SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = e.emp_no) AS fizetes
FROM `employees` AS e
LEFT JOIN `dept_emp` ON (`dept_emp`.`emp_no` = e.emp_no)
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`
WHERE
    (`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = e.emp_no)) AND
    (`dept_emp`.`to_date` = '9999-01-01') 
LIMIT 0, 20

/* Sorok megjelenítése 0-19 (összesen 20, A lekérdezés 0.0032 másodpercig tartott.) */

Mi a feladat? Mire kell leszűrni, rendezni?  

Rendezve:

/* Sorok megjelenítése 0-19 (összesen 20, A lekérdezés 0.0001 másodpercig tartott.) */

SELECT e.*, 
(SELECT  title FROM titles WHERE  titles.emp_no = e.emp_no AND from_date = (SELECT MAX(`from_date`) FROM `titles` WHERE titles.emp_no = e.emp_no)) AS beosztas,
(SELECT salary FROM salaries WHERE `salaries`.`emp_no` = e.emp_no AND from_date = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = e.emp_no)) AS fizetes
FROM `employees` AS e
LEFT JOIN `dept_emp` ON (`dept_emp`.`emp_no` = e.emp_no)
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`
WHERE
	(`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = e.emp_no)) AND
	(`dept_emp`.`to_date` = '9999-01-01') AND
    (SELECT salary FROM salaries WHERE `salaries`.`emp_no` = e.emp_no AND from_date = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = e.emp_no)) > 10000 AND
    (SELECT  title FROM titles WHERE  titles.emp_no = e.emp_no AND from_date = (SELECT MAX(`from_date`) FROM `titles` WHERE titles.emp_no = e.emp_no)) = "Staff"
    
    ORDER BY fizetes DESC
    
LIMIT 0, 20