06 Jan 2023

Posted on:

05 Jan 2023

0

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

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
;

Instructor
Posted on:

06 Jan 2023

0

Hi Akash!
Thanks for reaching out.

Your alternative solution using common table expressions is correct. Bravo!

Hope this helps.
Best,
Tsvetelin