SELECT e.emp_no as employee_ID, min(de.dept_no) as dept_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; I have 2 questions regarding the code above. Can you please reexplain the purporse of min() function above because I still don't get why chosing the minimum value of the department that one employee belongs to would be accurate? In the lecture, you said that we should join the dept_manager table to retrieve the manager_ID column. However, in the code, you chose to join the dept_emp table. Can you please help me understand why you chose to join this table instead?

Hello Vinh! I am also a student here but I will try to give you my view on it. As for 2. dept_manager was indeed used in retrieving the manager_ID column, see the part in your submitted code. (SELECT emp_no from dept_manager WHERE emp_no = 110022) as manager_ID dept_emp table was then used to retrieve dept_no for employees. There would be a possibility to use just dept_emp table instead of employees tables to get emp_no, but JOINing employees table with dept_emp will ensure, that employees are still working for the company. In dept_emp alone there are employees who no longer work for the company. As for 1., try SELECT e.emp_no, de.dept_no FROM t_dept_emp de JOIN t_employees e ON e.emp_no = de.emp_no WHERE e.emp_no = 10219 ORDER BY e.emp_no; You will see that employee 10219 could work in 2 different departments. Now when you use GROUP BY emp_no you know you will get just one record. But which department will it be? MIN(dept_no) ensure that you know which was it. The one with the lowest number. This is how I understood the query. Hope it helps. If you see some holes in the explanation, let me know. I am also learning :) Best, Maros J

back

Follow this topic

Vinh Nguyễn Thành

Last answered:

January 05, 2021

Posted on:

January 04, 2021

SQL Subqueries nested in SELECT and FROM. MIN() function and JOIN

1 answers ( 0 marked as helpful)

Maros Jevocin

Posted on:

January 05, 2021

Submit an answer

Content