need help with a tough SQL problem 1

[rsippos]

If you have three tables, EMPLOYEE (key: EMP_ID, fields: EMP_NAME, EMP_SALARY), DEPARTMENT (key: DPT_ID, fields: DPT_DESC, DPT_MGR_ID (FK to EMPLOYEE on EMP_ID)), and EMPLOYEE_DEPARTMENT (key:EMP_ID,DPT_ID),

How can I write a single SQL statement that would give the average employee salary for each department, along with the Manager's name and Department??

I can get the dpt_mgr_id, dpt_desc, and average salary, but I cannot tie in the manager name. Please help!

 

[swampBoogie]

select d.dpt_id,m.empname,avg(e.salary)
 from department d ,
      employee e,
      employee m,
      EMPLOYEE_DEPARTMENT ed
 where m.emp_id = d.dpt_mgr_id
   and d.dpt_id = ed.dpt_id
   and ed.emp_id = e.emp_id
 group by d.dpt_id,m.empname