Nathan van Beelen Nathan van Beelen - 1 year ago 55
SQL Question

How do I use a value from the superquery inside a subquery?

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;

But to get the last_name of the manager, you have to search the same table by the manager id you got from the employee. The solution I found is:

SELECT last_name,
(SELECT last_name FROM employees WHERE employee_id = manager_id),
FROM employees
WHERE manager_id IS NOT NULL;

However, it seems that 'manager_id' doesn't work in the subquery (although I expected that) and the output is NULL (for the manager id, all the other columns do have values).

So my question is, how can I use the manager_id in the subquery?

Side note: The manager_id can be different for each employee, so using a constant value doesn't work.

Answer Source

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
FROM employees e
WHERE e.manager_id IS NOT NULL;

The alias 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download