CODEWITHSUNDEEP
sqloracle-database
Test
Test

Reputation: 170

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, 
       employee_id, 
       (SELECT last_name FROM employees WHERE employee_id = manager_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.

Upvotes: 1

Views: 2393

Answers (3)

rohitvats
rohitvats

Reputation: 1851

When you want to refer to a table in the outer query, you need to either use the full table name like table.field or, as in your case, if the outer query table is same as the subquery table, you need to assign an alias to the outer query table and use it in the subquery like this:

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

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a self inner join ( a join with the same table)

SELECT 
      a.last_name
    , a.employee_id
    , b.last_name 
    , a.manager_id 
FROM employees  a 
INNER JOIN employees  b ON b.employee_id = a.manager_id;

The inner join work only if a.manager_id is not null so you can avoid this where condition

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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;

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.

Upvotes: 2

Related Questions