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:

-- This SQL query retrieves employee and manager details by joining the employees table with itself.

SELECT W1.employee_id as "Emp_id", -- Selects the employee ID column from the first instance of the employees table and labels it as "Emp_id"
       W1.last_name AS "Employee", -- Selects the last name column from the first instance of the employees table and labels it as "Employee"
       W2.employee_id AS "Manager ID", -- Selects the employee ID column from the second instance of the employees table and labels it as "Manager ID"
       W2.last_name AS "Manager" -- Selects the last name column from the second instance of the employees table and labels it as "Manager"
FROM employees W1 -- Specifies the first instance of the employees table and aliases it as "W1"
JOIN employees W2 -- Specifies the second instance of the employees table and aliases it as "W2"
ON W1.manager_id = W2.employee_id; -- Joins the first instance with the second instance based on the manager_id and employee_id

Explanation:

• This SQL query retrieves employee and manager details by joining the employees table with itself.
• The SELECT statement selects the employee ID and last name columns from both instances of the employees table and labels them appropriately.
• Two instances of the employees table are used, aliased as W1 and W2.
• The JOIN keyword is used to join the first instance (W1) with the second instance (W2) based on the manager_id and employee_id.
• The ON clause specifies the join condition where the manager_id from the first instance matches the employee_id from the second instance, indicating that the second instance represents the manager of the employee from the first instance.
• The result set will contain the employee ID, last name, manager ID, and manager's last name for each employee, showing the relationship between employees and their respective managers.

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.