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:

Go to:

• PostgreSQL JOINS Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : 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.

Practice Online

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