SELECT from multiple tables
11.4.2.4 use SELECT statement to retrieve data from multiple tables
Coding Club data
A result stores a student ID, a workshop ID and a score. To show a student's name or a workshop title, retrieve that information from its own table. The examples use the sample rows below.
| student_id (PK) | student_name | grade_level |
|---|---|---|
| 5 | Aidana Kairat | 11 |
| 8 | Alikhan Nurlybek | 11 |
| 13 | Alina Zhakup | 10 |
| 45 | Bekzat Sabir | 12 |
| student_id (PK + FK) | workshop_id (PK + FK) | score |
|---|---|---|
| 5 | 3 | 91 |
| 5 | 12 | 89 |
| 8 | 3 | 91 |
| 13 | 7 | 74 |
| workshop_id (PK) | title |
|---|---|
| 3 | Web Page Design |
| 4 | HTML Forms |
| 7 | SQL Data Queries |
| 12 | Data Normalisation |
PK means primary key. FK means foreign key. In results, the two IDs together form the primary key. Each ID also refers to a row in another table.
Find the student students.student_id = results.student_id
Find the workshop workshops.workshop_id = results.workshop_id
Example 1. Student names and scores
Show the student's name and score for each result. Write each field as table.column so it is clear where it comes from.
- SELECT
- List the columns to display.
- FROM
- List the tables, separated by commas.
- WHERE
- Match each foreign key with the primary key it refers to.
| student_name | score |
|---|---|
| Aidana Kairat | 89 |
| Aidana Kairat | 91 |
| Alikhan Nurlybek | 91 |
| Alina Zhakup | 74 |
Aidana appears twice because she has two results. A student with no matching result in these data has no row in the output.
Example 2. Student names, workshop titles and scores
Add workshops to FROM and its title to SELECT. Use AND in WHERE so both key comparisons must be true.
| student_name | title | score |
|---|---|---|
| Aidana Kairat | Web Page Design | 91 |
| Aidana Kairat | Data Normalisation | 89 |
| Alikhan Nurlybek | Web Page Design | 91 |
| Alina Zhakup | SQL Data Queries | 74 |
The result tables show one possible row order.
What happens if the workshop key comparison is missing?
Each of the four results would be paired with all four workshops. That gives 16 rows with workshop titles that may be wrong. The second comparison keeps only the title for the workshop ID stored in each result.
Exercises
Write one query using the sample data. Match the keys with = and combine comparisons with AND where needed.
Hint
Worked solution
See the appointments example in the BZFAR lesson on SQL DML.