It shows 40 row only inserted ! is it correct ?
insert into emp_manager select U.* from (
SELECT
A.*
FROM
(SELECT
e.emp_no AS employee_ID,
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 BETWEEN 10000 AND 10020) AS A union SELECT
B.*
FROM
(SELECT
e.emp_no AS employee_ID,
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 BETWEEN 10021 AND 10040) AS B union
SELECT
C.*
FROM
(SELECT
e.emp_no AS employee_ID,
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) AS C union
SELECT
D.*
FROM
(SELECT
e.emp_no AS employee_ID,
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) AS D) as U;
2 answers ( 0 marked as helpful)
For some reason, I get a return of 46 rows when I execute your query. I suggest going through the code line by line and comparing it with the attached solution from 365. Maybe execute the query each time you make a change to match the solution so you can see exactly where the issue was.
That is what I did to troubleshoot my own answer. I used the TRUNCATE command in between attempts since each time you execute, it adds more rows.
Good luck!
Tsvetelin Tsonkov
Instructor
Hi Yahia!
Thanks for reaching out.
You should use the GROUP BY clause. Please, use the following code:
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) 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) AS d) as u;
Hope this helps.
Best,
Tsvetelin