PostgreSQL JOINS: Make a join with table employees and itself to find the employee id, name, manager_id and manager name

4. Write a query to make a join with two tables employees and itself to find the employee id, last_name as Employee along with their manager_id and last name as Manager.

Sample Solution:

Code:

SELECT W1.employee_id as "Emp_id" , W1.last_name AS "Employee",
W2.employee_id AS "Manager ID", W2.last_name AS "Manager"
FROM employees W1 JOIN employees W2
ON W1.manager_id= W2.employee_id;

Sample table: employees

Output:

pg_exercises=# SELECT W1.employee_id as "Emp_id" , W1.last_name AS "Employee",
pg_exercises-# W2.employee_id AS "Manager ID", W2.last_name AS "Manager"
pg_exercises-# FROM employees W1 JOIN employees W2
pg_exercises-# ON W1.manager_id= W2.employee_id;
 Emp_id |  Employee   | Manager ID |  Manager
--------+-------------+------------+-----------
    103 | Hunold      |        102 | De Haan
    104 | Ernst       |        103 | Hunold
    105 | Austin      |        103 | Hunold
    106 | Pataballa   |        103 | Hunold
    107 | Lorentz     |        103 | Hunold
    114 | Raphaely    |        100 | King
    115 | Khoo        |        114 | Raphaely
    116 | Baida       |        114 | Raphaely
    117 | Tobias      |        114 | Raphaely
    101 | Kochhar     |        100 | King
    118 | Himuro      |        114 | Raphaely
    119 | Colmenares  |        114 | Raphaely
    133 | Mallin      |        122 | Kaufling
    134 | Rogers      |        122 | Kaufling
    135 | Gee         |        122 | Kaufling
    136 | Philtanker  |        122 | Kaufling
    138 | Stiles      |        123 | Vollman
    139 | Seo         |        123 | Vollman
    140 | Patel       |        123 | Vollman
    141 | Rajs        |        124 | Mourgos
    142 | Davies      |        124 | Mourgos
...
    203 | Mavris      |        101 | Kochhar
    204 | Baer        |        101 | Kochhar
    205 | Higgins     |        101 | Kochhar
    206 | Gietz       |        205 | Higgins
(105 rows)

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL query to make a join with three tables employees, departments and locations to find the name, including first_name and last_name, jobs, department name and ID, of the employees working in London.
Next: Write a query to make a join with a table employees and itself to find the name, including first_name and last_name and hire date for those employees who were hired after the employee Jones.