Last answered:

31 Aug 2022

Posted on:

29 Aug 2022

0

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)
Instructor
Posted on:

31 Aug 2022

0

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

Submit an answer