Employees with Departments and Salaries — Joining Multiple Tables in SQL

The problem

Helix Systems' payroll team needs a report listing each employee's name, the department they belong to, and their salary amount.

Write a query to return the employee name, department name, and salary amount for every salary record on file.

Assumptions:

An employee with multiple salary records appears once per salary in the result.

Output:

One row per salary record, with columns employee_name, department_name, and amount.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  e.name AS employee_name,
  d.name AS department_name,
  s.amount
FROM
  employees e
  JOIN departments d ON e.department_id = d.id
  JOIN salaries s ON e.id = s.employee_id

The shape

The chain reaches sideways from employees to departments for the dimension, and forward from employees to salaries for the fact. The result row count is driven by salaries, the one-to-many side — an employee with three salary records contributes three rows, each labelled with the same name and department.

Clause by clause

SELECT e.name AS employee_name, d.name AS department_name, s.amount pulls one column from each of the three tables. Both employees and departments have a column called name, so the alias prefix on each is what disambiguates them.
FROM employees e anchors the chain on employees.
JOIN departments d ON e.department_id = d.id attaches the department. Each employee belongs to one department, so this join doesn't multiply rows.
JOIN salaries s ON e.id = s.employee_id attaches every salary record for that employee. This is where the row count comes from — Sarah Chen's two salary records produce two rows in the result, each carrying her name and Engineering department, with the amount column distinguishing them.

You practiced a three-table chain where one of the participating tables is a one-to-many fact (salaries). The recurring rule: the result row count is driven by the most-multiplying table in the chain — here, salaries — not by the dimensions.

Return the employee name, department name, and salary amount for every salary record on file

The shape

Clause by clause

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.