Many-to-Many — Employees ↔ Projects via Junction Table
Follow employees through project_assignments to projects and return one row per assignment.
- Joins
- Sorting
Exercise brief
Understand the request
Project portfolio analyst A delivery roster needs the attributes stored on both sides of an employee-project many-to-many relationship.
List every employee-project assignment with the project's name and status. Many-to-many: an employee can be on multiple projects, a project can have multiple employees. Each assignment appears once. Return employee_id, first_name, last_name, project_id, project_name, status, allocation_pct — ordered by employee_id, project_id.
Return
- Return employee_id, first_name, last_name, project_id, project_name, status, allocation_pct in this exact left-to-right order.
Constraints
- Use the junction table as the relationship path.
- Do not join employees directly to projects.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)
project_assignments
employee_idINTEGERproject_idINTEGERallocation_pctINTEGER
projects
project_idINTEGERproject_nameVARCHARstatusVARCHAR
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Many-to-many shape: entity A → junction → entity B. THREE tables, TWO joins.
Hint 2
The junction table's PK is usually the COMPOSITE (employee_id, project_id) — preventing duplicate assignments.
Hint 3
Number of result rows = number of rows in the JUNCTION (12 here), NOT (employees × projects).
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, p.project_id, p.project_name, p.status, pa.allocation_pct FROM employees e INNER JOIN project_assignments pa ON e.employee_id = pa.employee_id INNER JOIN projects p ON pa.project_id = p.project_id ORDER BY e.employee_id, p.project_id;Why this works
Many-to-many is the third great relational primitive after one-to-one and one-to-many. The junction table holds the relationship; relationship attributes (start date, role, allocation) live there too. Always join entity-A → junction → entity-B in that order.
Success check
All 12 assignments appear exactly once with their employee and project attributes.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | first_name | last_name | project_id | project_name | status | allocation_pct |
|---|---|---|---|---|---|---|
| 100 | John | Smith | 1 | Apollo | Active | 50 |
| 100 | John | Smith | 2 | Bolt | Active | 50 |
| 101 | Alice | Johnson | 1 | Apollo | Active | 80 |
| 102 | Bob | Wilson | 1 | Apollo | Active | 60 |
| 102 | Bob | Wilson | 3 | Comet | Completed | 40 |
| 103 | Carol | Davis | 4 | Dawn | Planned | 100 |
| 104 | David | Brown | 2 | Bolt | Active | 70 |
| 105 | Emma | Taylor | 1 | Apollo | Active | 30 |
| 105 | Emma | Taylor | 2 | Bolt | Active | 40 |
| 105 | Emma | Taylor | 3 | Comet | Completed | 30 |
Previewing 10 of 12 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
Self Joins & Hierarchical Queries
Query organization charts, trees, and parent-child relationships.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
Open the interactive workspace and practice across SQL topics.