I have two complicated queries which I need to do an inner join between and I can't get the format correct.
The first query looks into the table that lists the manager of each department. As the table keeps a history of every change, I wanted to keep only the manager for each department with the latest from_date.
The department manager table looks like:
SELECT a.*
FROM
(SELECT d1.emp_no , d1.dept_no
FROM dept_manager d1
JOIN
(SELECT dept_no, MAX(from_date) AS Lastdate
FROM dept_manager
GROUP BY dept_no) d2 ON d1.from_date = d2.Lastdate
AND d1.dept_no = d2.dept_no) AS a;
Table looks like this:
| Emp_no | dept_no | from_date | to_date |
|---|---|---|---|
| 110022 | d001 | 1985-01-01 | 1991-10-01 |
| 110039 | d001 | 1991-10-01 | 9999-01-01 |
| 110085 | d002 | 1984-01-01 | 1989-12-17 |
| 110114 | d002 | 1989-12-17 | 9999-01-01 |
etc..
The second query is for the salaries of employees. As this table also keeps the salary history of each employee, I need to use (keep) only the most recent salary for any employee. The code I did was as follows:
SELECT b.*
FROM
(SELECT s1.emp_no , s1.salary
FROM salaries s1
JOIN
(SELECT MAX(from_date) AS Lastdate , emp_no
FROM salaries
GROUP BY emp_no) s2 ON s1.from_date = s2.Lastdate
AND s1.emp_no = s2.emp_no) AS b;
The table looks like:
| Emp_no | salary | from_date | to_date |
|---|---|---|---|
| 110001 | 45200 | 1991-01-01 | 1992-10-01 |
| 110001 | 47850 | 1992-01-01 | 1993-10-01 |
| 110001 | 52000 | 1993-10-01 | 1994-01-01 |
| 110022 | 35000 | 1985-01-01 | 1988-10-01 |
| 110022 | 36750 | 1988-01-01 | 1991-10-01 |
| 110022 | 38000 | 1991-10-01 | 1994-01-01 |
etc..
My objective is to get the average salary of all managers i.e. an inner join of the two complex queries shown above (the manager table and the salary table ).
What is the correct syntax for this?
