Manager name starting with letter-CodePudding

In a table with columns EMP_ID, EMP_NAME & MANAGER_ID fetch manager names for all employees whose names start with 'A'. Assume a manager is also an Employee.

Table: EMP

EMP_ID EMP_NAME MGR_ID

CodePudding user response：

Let's assume you don't have a manager's table, all you have is this employee table. Then,

SELECT EMP_NAME
FROM EMP 
WHERE EMP_ID IN (
   SELECT MGR_ID 
   FROM EMP
   WHERE EMP_NAME LIKE "A%"
)

should do the task.

CodePudding user response：

You simply need a self join -

SELECT E1.EMP_NAME EMPLOYEE_NAME, E2.EMP_NAME MANAGER_NAME
  FROM EMP E1
  LEFT JOIN EMP E2 ON E1.MGR_ID = E2.EMP_ID
 WHERE E1.EMP_NAME LIKE 'A%';