How to get the current manager's name for each employee and add it as a new column in the same table?

Question

I have a table Employees with emp_no and employees' info but there isn't a column specifying their manager at all.

The names of the managers and their emp_no are in the Employees table with the rest of the staff. The managers' emp_no and dept_no are present in the dept_manager table. The active managers have value 9999-01-01 in column to_date in dept_manager. In a 3rd table dept_emp, I have the emp_no and dept_no of each employee.

I tried to use left join to add the manager names against the employees'names but I get an error:

SELECT (concat(first_name, ' ',last_name)) as EmployeeName, 
(concat(first_name, ' ', last_name)) as Managersname,
FROM employees AS emp 
LEFT JOIN dept_manager AS dm ON emp.emp_no=dm.emp_no
LEFT JOIN dept_emp as de on dm.dept_no=de.dept_no and dm.to_date='9999-01-01';

Any better ideas about how to get the results right?

Thanks, GV

Answer 1

SELECT (concat(emp.first_name, ' ', emp.last_name)) as EmployeeName, 
(concat(dm.first_name, ' ', dm.last_name)) as Managersname 
FROM employees AS emp LEFT JOIN dept_manager AS dm ON emp.emp_no=dm.emp_no  
WHERE dm.to_date='9999-01-01';