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?
1 answers ( 0 marked as helpful)
Hi Prakhar!
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.
Best,
Tsvetelin