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.....
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.....
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
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
ASKER
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)
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)
ASKER
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!!
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;
PS: To format your code, select it and click on "code" in the upper menu.
ASKER
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!!!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
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.
ASKER
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;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So, are you saying that the order is CORRECT?
I am saying that there is no order.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
ASKER
I found the solution by process of elimination. The others here helped me to understand the solution I came up with.
Show us what you got done?
:p