How 2nd-Solution Provide Identical result set as first one?
SELECT e.first_name, e.last_name, e.hire_date, t.title, m.from_date, d.dept_name FROM employees e JOIN dept_manager m ON e.emp_no = m.emp_no JOIN departments d ON m.dept_no = d.dept_no JOIN titles t ON e.emp_no = t.emp_no AND m.from_date = t.from_date ORDER BY e.emp_no;
I just don't understand how this solution-2 works and provide same output as solution 1.
Mainly how and why
AND m.from_date = t.from_date
is added in 2nd solution. Can you help me to understand how above code deliver identical output as first one? and why we have join last two tables on multiple columns/fields?
Thanks for reaching out.
The answer stems in the fact that we include another condition in the last JOIN as you can see. We connect the titles table to the dept_manager one and if the from_date column from both tables coincide, we are sure that the employee will also be a manager since there are only managers in the dept_manager table.
Hope this helps.