Last answered:

21 Dec 2022

Posted on:

20 Dec 2022

0

Resolved: I got 41 rows for some reason can you please help

CREATE TABLE emp_manager (
    emp_no INT(11) NOT NULL,
    dept_no CHAR(4) NULL,
    manager_no INT(11) NOT NULL
);

INSERT INTO emp_manager
SELECT U.* FROM
(SELECT A.*
FROM
(SELECT
    e.emp_no AS employee_ID,
    MIN(de.dept_no) AS department_code,
    (SELECT
            emp_no
        FROM
            dept_manager
        WHERE
            emp_no = 110022) AS manager_ID
FROM
    employees e
        JOIN
    dept_emp de ON e.emp_no = de.emp_no
WHERE
    e.emp_no <= 10020
GROUP BY e.emp_no
ORDER BY e.emp_no)AS A
UNION
SELECT B.*
FROM
(SELECT
    e.emp_no AS employee_ID,
    MIN(de.dept_no) AS department_code,
    (SELECT
            emp_no
        FROM
            dept_manager
        WHERE
            emp_no = 110039) AS manager_ID
FROM
    employees e
        JOIN
    dept_emp de ON e.emp_no = de.emp_no
WHERE
    e.emp_no < 10020
GROUP BY e.emp_no
ORDER BY e.emp_no
LIMIT 20)AS B
UNION
SELECT C.*
FROM
(SELECT
    e.emp_no AS employee_ID,
    MIN(de.dept_no) AS department_code,
    (SELECT
            emp_no
        FROM
            dept_manager
        WHERE
            emp_no = 110039) AS manager_ID
FROM
    employees e
        JOIN
    dept_emp de ON e.emp_no = de.emp_no
WHERE
    e.emp_no =110022
GROUP BY e.emp_no
ORDER BY e.emp_no)AS C
UNION
SELECT D.*
FROM
(SELECT
    e.emp_no AS employee_ID,
    MIN(de.dept_no) AS department_code,
    (SELECT
            emp_no
        FROM
            dept_manager
        WHERE
            emp_no = 110022) AS manager_ID
FROM
    employees e
        JOIN
    dept_emp de ON e.emp_no = de.emp_no
WHERE
    e.emp_no =110039
GROUP BY e.emp_no
ORDER BY e.emp_no)AS D) as U ;

1 answers ( 1 marked as helpful)
Instructor
Posted on:

21 Dec 2022

1

Hi Andrew!
Thanks for reaching out.

The reason for the different result set comes from the WHERE condition. You used:
.............
WHERE
    e.emp_no < 10020
.............
instead of:
.............
WHERE
    e.emp_no <= 10020
.............
We should also include the equal (=) sign. So, we will count emp_no=10020 also.

Hope this helps.
Best,
Tsvetelin

Submit an answer