WITH de_l(emp_no, dept_no) AS(SELECT d.emp_no, d.dept_no FROM dept_emp d INNER JOIN (SELECT emp_no, MAX(from_date) AS maxdate FROM dept_emp GROUP BY emp_no) AS tm ON d.emp_no = tm.emp_no AND d.from_date = tm.maxdate), e_below_10020 AS (SELECT e.emp_no FROM employees e WHERE e.emp_no <=10020), e_above_10020 AS (SELECT e.emp_no FROM employees e WHERE e.emp_no >10020 AND e.emp_no <=10040), manager_110022 AS (SELECT dm.emp_no AS manager_id FROM dept_manager dm WHERE dm.emp_no = 110022), manager_110039 AS (SELECT dm.emp_no AS manager_id FROM dept_manager dm WHERE dm.emp_no = 110039), subset_a AS (SELECT e_below_10020.emp_no, de_l.dept_no, manager_110022.manager_id FROM e_below_10020 INNER JOIN de_l ON e_below_10020.emp_no = de_l.emp_no CROSS JOIN manager_110022), subset_b AS (SELECT e_above_10020.emp_no, de_l.dept_no, manager_110039.manager_id FROM e_above_10020 INNER JOIN de_l ON e_above_10020.emp_no = de_l.emp_no CROSS JOIN manager_110039) SELECT * FROM subset_a UNION SELECT * FROM subset_b ;

Hi Akash! Thanks for reaching out. Your alternative solution using common table expressions is correct. Bravo! Hope this helps. Best, Tsvetelin

Follow this topic

Last answered:

06 Jan 2023

Posted on:

05 Jan 2023

I have solved it using CTE. I have arrived at the similar output.

1 answers ( 0 marked as helpful)

Instructor

Posted on:

06 Jan 2023