PostgreSQL JOINS: Make a join with three tables departments, employees and locations to display the department name, manager name, and city

9. Write a query to make a join with three tables departments, employees, and locations to display the department name, manager name, and city.

Sample Solution:

Code:

-- This SQL query retrieves department name, manager's first name, and city from the departments, employees, and locations tables.

SELECT w1.department_name, -- Selects the department_name column from the departments table
       w2.first_name, -- Selects the first_name column from the employees table
       w3.city -- Selects the city column from the locations table
FROM departments w1 -- Specifies the first table from which to retrieve data, aliasing it as 'w1'
JOIN employees w2 -- Joins the departments table with the employees table, specifying the second table and aliasing it as 'w2'
ON (w1.manager_id = w2.employee_id) -- Specifies the join condition based on the manager_id and employee_id columns
JOIN locations w3 USING (location_id); -- Joins the result with the locations table based on the location_id, using the USING keyword

Explanation:

• This SQL query retrieves department name, manager's first name, and city from the departments, employees, and locations tables.
• The SELECT statement selects the department name, manager's first name, and city columns.
• The FROM clause specifies the first table from which to retrieve data, which is the departments table, aliased as w1.
• A JOIN operation is performed between the departments table (w1) and the employees table (w2), specifying the second table and aliasing it as w2.
• The ON clause specifies the join condition where the manager_id from the departments table matches the employee_id from the employees table.
• Another JOIN operation is performed to join the result with the locations table (w3) based on the location_id, using the USING keyword to specify the column common to both tables.
• The result set will contain department name, manager's first name, and city for each department where a manager is assigned, retrieved from the departments, employees, and locations tables.

Sample table: employees

Sample table: departments

Sample table: locations

Output:

pg_exercises=# SELECT w1.department_name, w2.first_name, w3.city
pg_exercises-# FROM departments w1
pg_exercises-# JOIN employees w2
pg_exercises-# ON (w1.manager_id = w2.employee_id)
pg_exercises-# JOIN locations w3 USING (location_id);

 department_name  | first_name |        city
------------------+------------+---------------------
 IT               | Alexander  | Southlake
 Purchasing       | Den        | Seattle
 Executive        | Steven     | Seattle
 Sales            | John       | OX9 9ZB
 Shipping         | Adam       | South San Francisco
 Finance          | Nancy      | Seattle
 Administration   | Jennifer   | Seattle
 Marketing        | Michael    | Toronto
 Human Resources  | Susan      | London
 Public Relations | Hermann    | Munich
 Accounting       | Shelley    | Seattle
(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 with two tables employees and departments to display the department ID, department name and the first name of the manager.
Next: Write a query to make a join with two tables employees and jobs to display the job title and average salary of employees.