w3resource

PostgreSQL JOINS: Make a join with three tables to find the name, jobs, department name and ID of all the employees working in London


3. Write a SQL query to make a join with three tables employees, departments and locations to find the name, including first_name and last_name, jobs, department name and ID, of the employees working in London.

Sample Solution:

Code:

-- This SQL query retrieves employee details along with their department information for employees working in London.

SELECT e.first_name, -- Selects the first_name column from the employees table
       e.last_name, -- Selects the last_name column from the employees table
       e.job_id, -- Selects the job_id column from the employees table
       e.department_id, -- Selects the department_id column from the employees table
       d.department_name -- Selects the department_name column from the departments table
FROM employees e -- Specifies the first table from which to retrieve data, in this case, the employees table
JOIN departments d -- Joins the employees table with the departments table
ON (e.department_id = d.department_id) -- Specifies the join condition based on the department_id column
JOIN locations l ON -- Joins the departments table with the locations table
(d.location_id = l.location_id) -- Specifies the join condition based on the location_id column
WHERE l.city = 'London'; -- Filters the rows to include only those where the city is 'London'

Explanation:

  • This SQL query retrieves employee details along with their department information for employees working in London.
  • The SELECT statement selects the first name, last name, job ID, department ID, and department name columns.
  • The FROM clause specifies the first table from which to retrieve data, which is the employees table, aliased as e.
  • The JOIN keyword is used to join the employees table (e) with the departments table (d) based on the department_id column.
  • The ON clause specifies the join condition based on the matching department_id.
  • Another JOIN operation is performed to join the departments table (d) with the locations table (l) based on the location_id column.
  • The WHERE clause filters the rows to include only those where the city is 'London'.
  • The result set will contain employee details along with their department information for employees working in London.

Sample table: employees


Sample table: locations


Sample table: departments


Output:

pg_exercises-# FROM employees e
pg_exercises-# JOIN departments d
pg_exercises-# ON (e.department_id = d.department_id)
pg_exercises-# JOIN locations l ON
pg_exercises-# (d.location_id = l.location_id)
pg_exercises-# WHERE l.city = 'London';
 first_name | last_name | job_id | department_id | department_name
------------+-----------+--------+---------------+-----------------
 Susan      | Mavris    | HR_REP |            40 | Human Resources
(1 row)

Relational Algebra Expression:

Relational Algebra Expression: Make a join with three tables to find the name, jobs, department name and ID of all the employees working in London.

Relational Algebra Tree:

Relational Algebra Tree: Make a join with three tables to find the name, jobs, department name and ID of all the employees working in London.

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 employees and departments table to find the name of the employee, including first_name and last name, department ID and name of departments.
Next: Write a query to make a join with two tables employees and itself to find the employee id, last_name as Employee along with their manager_id and last name as Manager.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.