I need to create a query that shows the last name of an employee, the employee id, the last name of the manager of that employee and the id of that manager.
The last name, id, and the manager id of that employee is easy to do because it is already in one row, which means that the following is sufficient:
SELECT last_name, employee_id, manager_id FROM employees WHERE manager_id IS NOT NULL;
(SELECT last_name FROM employees WHERE employee_id = manager_id),
WHERE manager_id IS NOT NULL;
What you need is a correlated subquery. I strongly, strongly recommend that you use table aliases and qualified column names in all your queries. However, these are particularly important with correlated subqueries.
You should write this query as:
SELECT e.last_name, e.employee_id, (SELECT m.last_name FROM employees m WHERE m.employee_id = e.manager_id ), e.manager_id FROM employees e WHERE e.manager_id IS NOT NULL;
e is an abbreviation for the table reference to
employees in the outer query. The alias
m is an abbreviation for the table reference in the subquery.
Notice that all column references use the table alias. This makes the query unambiguous, can prevent unexpected errors, and makes the query much easier for you and others to understand.