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:

-- 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 Expression: Make a join with three tables departments, employees and locations to display the department name, manager name, and city.

Relational Algebra Tree:

Relational Algebra Tree: 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.

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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.