I have three different tables salaries, dept_managers(Department managers), and departments the first two tables are connected through emp_no(Employee number), While the other two tables are connected through dept_no(the department number), I want to have a query that gives me the average amount of salaries that each department has. I tried this query but It did not work.

SELECTd.dept_name,dm.dept_noFROM departments d
    JOIN dept_manager dm ON dm.dept_no = d.dept_no
    JOIN(       
            SELECT AVG(salary) AS Average_salary
           FROM salaries s
           GROUP BY salary)
           ON d.emp_no = dm.emp_no
    ; 

CodePudding user response：

maybe I'm missing something here but wouldn't you just join department to employees and then join salaries to employees and get the avg salary grouped by department?

   select d.dept_name, avg(s.salary)
        from departments d join employees e on (e.dept_no = d.dept_no)
        join salaries s on (s.emp_no = e.emp_no)
        group by d.dept_name