Search code examples
mysqlsqldatabaseoracle-databasecorrelated-subquery

SQL Correlated Subquery in Select

Is it possible to create a correlated subquery in SELECT just like this?

SELECT  EmployeeID,
EmployeeName,
ManagerID,
    (SELECT EmployeeName FROM tblEmployee WHERE EmployeeID = ManagerID)
FROM tblEmployee 
WHERE EmployeeID = '12345';


The ManagerID should be equal and should be the ManagerID of Employee '12345'.
The purpose of the subquery is to fetch the Manager's Fullname based on its ID.

I highly appreciate your response.
I am a newbie.

Thanks,
Rye.

Solution

  • Yes. To simplify your SQL writing, remember to always qualify all column names when your query has more than one table reference:

    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID,
       (SELECT m.EmployeeName
        FROM tblEmployee m
        WHERE m.EmployeeID = e.ManagerID
       )
FROM tblEmployee e
WHERE e.EmployeeID = '12345';