Link to home
Start Free TrialLog in
Avatar of svillardi
svillardi

asked on

ORACLE SQL Query - multiple tables, joins

Hi Experts,

I need help with a query.

Table 1:  Employees
Empl_id
last_name
Mgr_id
Salary
Dept_id

Table 2:  Departments
Dept_id
Dept_name
Mgr_id

The goal is to create a list of department names, the manager id, last name of mgr, and average salary for each department.

I know I have to join the empl and dept tables to each other and then somehow join the employees table to itself.

I can get the proper average as long as I don't add the mgr_id, then the groups break and its a mess............................

This is for a class so any help would be appreciated.  As part of the solution, what I want to do is to understand the order of the joins and how that makes a difference in the outcome.

Thank you,

S.....
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

This is a classic homework assignment....
Show us what you got done?
:p
Avatar of svillardi
svillardi

ASKER

You asked for it... I've struggled with it for hours...  remember, these are all works in progress.  I am trying to take this apart piece by piece...

SELECT d.department_name, m.manager_id, m.last_name, TRUNC(AVG(m.salary),0)
FROM departments d JOIN employees m
ON d.department_id = m.department_id
JOIN employees e
ON (m.employee_id = e.manager_id)
GROUP BY d.department_name, m.manager_id, m.last_name



SELECT d.department_name, w.last_name AS "Manager", AVG(w.salary)
FROM employees w JOIN employees m
ON (w.manager_id = m.employee_id)
JOIN departments d
on (m.department_id = d.department_id)
GROUP BY d.department_name, w.last_name

This one (because it's incomplete, of course... is the only one which maintains the correct average salary)

SELECT d.department_name, AVG(m.salary)
FROM employees m join departments d
ON (m.department_id = d.department_id)
GROUP BY d.department_name
Now, I am just cutting and pasting here........so I am duplicating work, I'm sure....

SELECT d.department_name, m.manager_id, m.employee_id TRUNC(AVG(e.salary),0)
FROM department d JOIN employees e
ON d.department_id = e.department_id
JOIN employees m
ON (e.employee_id = m.manager_id)
GROUP BY d.department_name, m.manager_id, m.employee_id

select *
from departments

SELECT d.department_name, e.manager_id, e.salary
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id) JOIN
departments d
ON (e.department_id = d.department_id)
Now, here's one I figured out that was a little simpler, but it has a key component -- the employees table is joined to itself....I don't quite fully understand it, but I know that it allows one field to equal another......  how, I don't get that part... LOL  So I tried to use the same approach in my other problem.

SELECT w.last_name AS "Employee", w.hire_date AS "Emp Hired", m.last_name AS "Manager", m.hire_date AS "Mgr Hired"
FROM employees w LEFT OUTER JOIN employees m
ON (w.manager_id = m.employee_id)
WHERE w.hire_date < m.hire_date

THANKS!!
You are on the right track, is this what you need?:
SELECT d.department_name AS "Department"
     , m.last_name AS "Manager"
     , m.hire_date AS "Mgr Hired"
     , AVG ( w.salary) "Avg Salary"
  FROM departments s
       LEFT OUTER JOIN employees m ON (m.employee_id = d.manager_id)
       LEFT OUTER JOIN employees w ON (w.manager_id = m.employee_id)
 WHERE w.employee_id != m.employee_id
GROUP BY d.department_name, m.last_name, m.hire_date;

Open in new window

PS: To format your code, select it and click on "code" in the upper menu.
No.  This isn't it....lol  But I am trying to understand the affects of moving things around.  Can you help me to build it rather than building it for me?

It must have to do with the order of the SELECT fields and the joins, but I can't figure it out.  Partly because the totals of the average salary keep changing....

Columns:  Department Name, Manager_ID, Mgr Last Name, Avg_Dept_Salary

I need to understand the "rules" of putting it together.

Thanks for your help!!!
ASKER CERTIFIED SOLUTION
Avatar of svillardi
svillardi

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Some more confusing parts--  
Am I joining A to B to C, or,  A to B and A to C on my answer?  Also, if I have 2 FK does it matter which one I choose to join it on?  If so why?  I think understanding how the join works is what's really frustrating me.

Also my correct answer doesn't match the assignment 100%.  There are two rows (out of which are reversed, and I don't think there is anything I can do about it.  There are 6 different combinations of GROUP BY, and only the one I wrote comes close.   I don't even understand what criteria the GROUP BY uses to determine the order.

The bold and italicized rows are reversed in the assignment:

DEPARTMENT_NAME MANAGER_ID MANAGER_NAME AVG_DEPT_SALARY
Administration 200 Whalen 4400
IT 103 Hunold 6400
Marketing 201 Hartstein 9500
Shipping 124 Mourgos 3500
Accounting 205 Higgins 10150
Executive 100 King 19333

Sales 149 Zlotkey 10033
If you are interested in what order your tables are joined, you need to look at the explain plan.  There are numerous articles here on how to generate the plan.

A GROUP BY does not guarantee any type of order.  If  you are looking for a specific order then you must include a ORDER BY clause.
I think the problem is in the joins.  I've tried all combinations, and this one is the only one which is close:

SELECT d.department_name, d.manager_id, m.last_name AS "MANAGER_NAME",  ROUND(AVG(e.SALARY))AS "AVG_DEPT_SALARY"
FROM employees e 
JOIN departments d ON e.department_id = d.department_id
JOIN employees m ON d.manager_id = m.employee_id
GROUP BY d.department_name, d.manager_id, m.last_name;

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK, thanks.  So why would the results be SLIGHTLY different?  I have 2 rows reversed out of 7.

I can't change any of the SELECT of GROUP BY because I'm tied into those columns.

I can only think that the assignment results are wrong.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
So, are you saying that the order is CORRECT?
I am saying that there is no order.
OK, we're going back and forth a bit.  I get your point, that the only way to control order is with an ORDER BY clause.

The problem is that the result listed as the answer in the problem is different than the result l came up with -- on 2 rows.  They have the same information, but not the same order.  All other rows are where they are supposed to be.

How is that possible?

All other problems have the EXACT same result.  We are being graded on that.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Which means that the query is correct.  Right?  (Please say yes... LOL)
I did say yes.  3 posts ago.

See post 39672890

The results of those queries are not different.  They are the same.  They are in a different order, but it is the same set of results.
I am sorry but I didn't understand that the post was saying that the answer was correct. Only that all three queries produced the same results.   Much thanks for your patience and discussion.
I found the solution by process of elimination.  The others here helped me to understand the solution I came up with.