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:

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

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.