tips and tricks for JOINs query from the video
Hi,
Why we are not using WHERE clause (WHERE t.title = 'Manager') , or add an AND operator (JOIN salaries s ON s.emp_no = m.emp_no AND m.from_date = s.from_date) as it was done in the previous query? We can have a Manager, that can have more than one record in the Salary Table (for example 100k for X period when he was a manager and 200k for Y period when he was not a manager)
Shouldn't the query look like this?
SELECT d.dept_name, AVG(s.salary)
FROM employees.dept_manager m
JOIN employees.departments d ON m.dept_no = d.dept_no
JOIN employees.salaries s ON s.emp_no = m.emp_no
JOIN employees.titles t ON t.emp_no = m.emp_no
WHERE t.title = 'Manager'
GROUP BY d.dept_name
ORDER BY d.dept_no, m.emp_no;
Best,
Adam
Hi Adam!
Thanks for reaching out.
Actually, it is the same. If you compare the two result sets from your query and the query presented in the course you will notice that they are the same. It is because this check if the employee is a manager is unnecessary. If an employee is in the dept_manager table, he/she is a manager.
Hope this helps.
Best,
Tsvetelin
Hi Tsvetelin,
I still believe that the solution is not correct here...
I exported the data to excel and checked it.
Both queries (mine & yours) calculate the average salary based on all salaries that those particular Managers have(the WHERE clause did not work as I expected it to work). But shouldn't we take only the salaries from the period they were managers? For Example dept_no d001 - we are taking every salary listed on the screen in column B, but shouldn't we take only those coloured in green? those are salaries from the periods, when 110022 and 110039 were managers.
