In my database, I have a table salaries which stores all past or current salaries from the employees, and a table employees which stores my employees.

I am trying to select all employees that got a promotion:

select first_name, last_name
from salaries
join employees on employees.emp_no = salaries.emp_no
where salaries.from_date > employees.hire_date
  and salary >
  (select salary from salaries
   where salaries.emp_no = employees.emp_no and from_date = employees.hire_date)
group by salaries.emp_no
limit 10 ;

It returns:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Georgi     | Facello   |
| Chirstian  | Koblick   |
| Kyoichi    | Maliniak  |
| Tzvetan    | Zielinski |
| Sumant     | Peac      |
| Mary       | Sluis     |
| Patricio   | Bridgland |
| Eberhardt  | Terkki    |
| Cristinel  | Bouloucos |
| Kazuhide   | Peha      |
+------------+-----------+
10 rows in set (10.05 sec)

Now I am trying to select at the same time the salaries that they have when they have been hired (because it is already selected in a nested select).

Is there any way to fetch this data from select salary from salaries where salaries.emp_no = employees.emp_no and from_date = employees.hire_date ?

I have tried something like...

select first_name, last_name, first_salary.salary
from salaries join employees on employees.emp_no = salaries.emp_no
where salaries.from_date > employees.hire_date and salary >
  (select salary from salaries where salaries.emp_no = employees.emp_no and from_date = employees.hire_date)
   AS FIRST_SALARY
group by salaries.emp_no
limit 10 ;

... but it cleary does not work

(We could otherwise do an UNION because the query is already very long).

One way is to use MAX and MIN on salary:

SELECT employees.emp_no, first_name, last_name, MAX(salary), MIN(salary)
FROM salaries
INNER JOIN employees ON employees.emp_no = salaries.emp_no
GROUP BY employees.emp_no, first_name, last_name
HAVING MAX(salary) > MIN(salary)

Edit: if you want to ensure that the max salary actually came at a later date than the hire date, you could do it this way:

SELECT first_name, last_name
FROM
(SELECT employees.emp_no, first_name, last_name, MAX(salary) AS mxsal
 FROM employees
 INNER JOIN salaries
 ON employees.emp_no = salaries.emp_no
 GROUP BY employees.emp_no, first_name, last_name) highest
INNER JOIN
(SELECT employees.emp_no, salary
 FROM employees
 INNER JOIN salaries on employees.emp_no = salaries.emp_no
 WHERE employees.hire_date = salaries.from_date) starting
ON highest.emp_no = starting.emp_no
WHERE highest.mxsal > starting.salary

So first I created a "highest" table with the max salary per employee (mxsal). Then I joined this one with a "starting" table which contains the starting salary for each employee (starting.salary). Then only selected the records where the max salary is greater than the starting one.

more