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

Question

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?

Answer 1

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

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

Submit an answer