PostgreSQL JOINS: Make a join with two tables departments and employees to display the department ID, department name and the first name of the manager

8. Write a query to make a join with two tables employees and departments to display the department ID, department name and the first name of the manager.

Sample Solution:

Code:

-- This SQL query retrieves department ID, department name, manager ID, and manager's first name by joining the departments and employees tables.

SELECT w1.department_id, -- Selects the department_id column from the first instance of the departments table
       w1.department_name, -- Selects the department_name column from the first instance of the departments table
       w2.manager_id, -- Selects the manager_id column from the second instance of the employees table
       w2.first_name -- Selects the first_name column from the second instance of the employees table
FROM departments w1 -- Specifies the first instance of the departments table and aliases it as 'w1'
INNER JOIN employees w2 -- Performs an inner join with the employees table, specifying the second instance and aliasing it as 'w2'
ON (w1.manager_id = w2.employee_id); -- Specifies the join condition where the manager_id from the first instance matches the employee_id from the second instance

Explanation:

• This SQL query retrieves department ID, department name, manager ID, and manager's first name by joining the departments and employees tables.
• The SELECT statement selects the department ID, department name, manager ID, and manager's first name columns.
• Two instances of the tables are used, aliased as w1 for the departments table and w2 for the employees table.
• An INNER JOIN operation is performed to join the departments table (w1) with the employees table (w2).
• The ON clause specifies the join condition where the manager_id from the departments table matches the employee_id from the employees table, indicating that the employee is a manager of the department.

Sample table: employees

Sample table: departments

Output:

pg_exercises=# SELECT w1.department_id, w1.department_name, w2.manager_id, w2.first_name
pg_exercises-# FROM departments w1
pg_exercises-# INNER JOIN employees w2
pg_exercises-# ON (w1.manager_id = w2.employee_id);

 department_id | department_name  | manager_id | first_name
---------------+------------------+------------+------------
            60 | IT               |        102 | Alexander
            30 | Purchasing       |        100 | Den
            90 | Executive        |          0 | Steven
            80 | Sales            |        100 | John
            50 | Shipping         |        100 | Adam
           100 | Finance          |        101 | Nancy
            10 | Administration   |        101 | Jennifer
            20 | Marketing        |        100 | Michael
            40 | Human Resources  |        101 | Susan
            70 | Public Relations |        101 | Hermann
           110 | Accounting       |        101 | Shelley
(11 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

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

Previous: Write a query to make a join to find the employee ID, job title and number of days an employee worked, for all the employees who worked in a department which ID is 90.
Next: Write a query to make a join with three tables departments, employees, and locations to display the department name, manager name, and city.