Querying with JUNCTION Tables

tags: #sql/complex_queries

To make queries using junction tables, you would typically use a combination of JOIN and WHERE clauses (very similar to regular Multiway Joins).

Consider the following tables:

employees:
+----+----------+
| id |   name   |
+----+----------+
|  1 | John     |
|  2 | Jane     |
|  3 | Michael  |
|  4 | Samantha |
+----+----------+

projects:
+----+-----------------+
| id |      name       |
+----+-----------------+
|  1 | Project A       |
|  2 | Project B       |
|  3 | Project C       |
|  4 | Project D       |
+----+-----------------+

employees_works_project:
+-------------+-------------+
| employee_id | project_id  |
+-------------+-------------+
| 1           | 1           |
| 1           | 2           |
| 2           | 1           |
| 3           | 3           |
| 4           | 2           |
| 4           | 4           |
+-------------+-------------+

To query the employees who are working on a specific project, you would use a join on the employees, employees_works_project, and projects tables, like this:

SELECT employees.name, projects.name AS project_name
FROM employees
JOIN employees_works_project ON employees.id = employees_works_project.employee_id
JOIN projects ON employees_works_project.project_id = projects.id
WHERE projects.name = 'Project A';

This returns the following:

+------+--------------+
| name | project_name |
+------+--------------+
| John | Project A    |
| Jane | Project A    |
+------+--------------+