w3resource

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)

Practice Online


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

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming