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:

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

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.