Joining of two complex MySQL queries-CodePudding

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?

CodePudding user response：

SELECT COUNT(salary),t1.emp_no,dept_no FROM salary t1
JOIN manager t2 ON t1.emp_no=t2.emp_no
GROUP BY t1.emp_no

Here you have the SIM. As your date some Times are year 9999, avg is strange to do.

CodePudding user response：

If I understand your query well (avg salary of current salary). Here is an example : https://www.db-fiddle.com/f/47iAMRgXRAGgg34gFG58JD/0

However this doesn't work with current table because newest employees does not have salary in table.