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.

students
student_id (PK)student_namegrade_level
5Aidana Kairat11
8Alikhan Nurlybek11
13Alina Zhakup10
45Bekzat Sabir12
results
student_id (PK + FK)workshop_id (PK + FK)score
5391
51289
8391
13774
workshops
workshop_id (PK)title
3Web Page Design
4HTML Forms
7SQL Data Queries
12Data 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.
SELECT students.student_name, results.score FROM students, results WHERE students.student_id = results.student_id;
Example result
student_namescore
Aidana Kairat89
Aidana Kairat91
Alikhan Nurlybek91
Alina Zhakup74

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.

SELECT students.student_name, workshops.title, results.score FROM students, results, workshops WHERE students.student_id = results.student_id AND workshops.workshop_id = results.workshop_id;
Example result
student_nametitlescore
Aidana KairatWeb Page Design91
Aidana KairatData Normalisation89
Alikhan NurlybekWeb Page Design91
Alina ZhakupSQL Data Queries74

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.