All subjects · Information Technology · WIT14 3 hour practical exam on database solutions
Queries across tables: joins
Edexcel IAL Information Technology · WIT14 3 hour practical exam on database solutions
Most exam queries need data from more than one table: a customer's name with their hire dates, or a bike's rate with the hire length. A join follows the relationship between the tables using the primary and foreign keys. Unit 1 introduced the JOIN syntax; this lesson applies it to multi-table exam queries, in SQL and in the Access design grid.
INNER JOIN
An inner join returns rows where the key values match in both tables. The ON clause states the match. Short aliases (c, h) save typing:
SELECT c.FirstName, c.Surname, h.HireDate, h.Days
FROM tblCustomer c
INNER JOIN tblHire h ON h.CustomerID = c.CustomerID
WHERE c.Town = 'Portview'
ORDER BY h.HireDateResult (5 rows):
FirstName | Surname | HireDate | Days
----------+---------+------------+-----
Amira | Hassan | 2026-03-02 | 2
Chen | Li | 2026-03-05 | 1
Amira | Hassan | 2026-03-20 | 1
Chen | Li | 2026-04-18 | 1
Amira | Hassan | 2026-05-01 | 3In an Access design view you add both tables; the relationship line between them is the join.
Joining through a link table
To combine hires with bikes you go through tblHireItem, joining three tables (or four for customer names). Calculations can use fields from any joined table:
SELECT h.HireID, b.Model, b.DailyRate * h.Days AS Cost
FROM tblHire h
JOIN tblHireItem i ON i.HireID = h.HireID
JOIN tblBike b ON b.BikeID = i.BikeID
WHERE h.HireDate LIKE '2026-04%'
ORDER BY h.HireID, b.ModelResult (4 rows):
HireID | Model | Cost
-------+------------+------
105 | Commuter | 20.00
105 | Ridge Pro | 44.00
106 | Trail 500 | 72.00
107 | City Glide | 12.50LEFT JOIN: finding what is missing
A LEFT JOIN keeps every row from the left table even when there is no match, filling the right side with NULL. Adding WHERE ... IS NULL finds records with no related records, such as customers who have never hired:
SELECT c.FirstName, c.Surname, h.HireID
FROM tblCustomer c
LEFT JOIN tblHire h ON h.CustomerID = c.CustomerID
WHERE h.HireID IS NULLResult (1 row):
FirstName | Surname | HireID
----------+---------+-------
Farah | Ahmed | NULLIn Access this is a join where you choose 'include all records from tblCustomer' in Join Properties, or the Find Unmatched Query Wizard.
Common join mistakes
- Missing join condition: listing two tables with no ON gives every combination of rows (a Cartesian product): 6 customers x 8 hires = 48 rows of nonsense.
- Ambiguous field names: CustomerID exists in both tables, so write c.CustomerID or h.CustomerID.
- Joining on the wrong fields: always join a foreign key to the primary key it refers to.
Worked example
List every hire by Amira Hassan with the model of each bike, earliest first.
- ```sql SELECT h.HireDate, b.Model FROM tblCustomer c JOIN tblHire h ON h.CustomerID = c.CustomerID JOIN tblHireItem i ON i.HireID = h.HireID JOIN tblBike b ON b.BikeID = i.BikeID WHERE c.FirstName = 'Amira' AND c.Surname = 'Hassan' ORDER BY h.HireDate, b.Model ```
- Result (5 rows): ``` HireDate | Model -----------+----------- 2026-03-02 | City Glide 2026-03-02 | Trail 500 2026-03-20 | Volt E2 2026-05-01 | Speedster 2026-05-01 | Volt E2 ```
Answer: Four tables joined along the relationships; one row per bike per hire.
Check yourself
- How many rows does this query return on the Harbour Bike Hire database?
```sql
SELECT c.FirstName, c.Surname, h.HireDate, h.Days
FROM tblCustomer c
INNER JOIN tblHire h ON h.CustomerID = c.CustomerID
WHERE c.Town = 'Portview'
ORDER BY h.HireDate
```
Show answer
5. Portview customers are Amira (3 hires) and Chen (2 hires): 5 rows.
- How many rows does this query return on the Harbour Bike Hire database?
```sql
SELECT h.HireID, b.Model, b.DailyRate * h.Days AS Cost
FROM tblHire h
JOIN tblHireItem i ON i.HireID = h.HireID
JOIN tblBike b ON b.BikeID = i.BikeID
WHERE h.HireDate LIKE '2026-04%'
ORDER BY h.HireID, b.Model
```
Show answer
4. April hires and their bikes: 105, Commuter, 20.00; 105, Ridge Pro, 44.00; 106, Trail 500, 72.00; 107, City Glide, 12.50. That is 4 rows.
- What is the total cost of hire 105?
```sql
SELECT SUM(b.DailyRate * h.Days) FROM tblHire h JOIN tblHireItem i ON i.HireID = h.HireID JOIN tblBike b ON b.BikeID = i.BikeID WHERE h.HireID = 105
```
Show answer
64.00. Hire 105: Ridge Pro 22.00 and Commuter 10.00 for 2 days = 64.00.
- Which result does this query give on the Harbour Bike Hire database?
```sql
SELECT c.FirstName, c.Surname, h.HireID
FROM tblCustomer c
LEFT JOIN tblHire h ON h.CustomerID = c.CustomerID
WHERE h.HireID IS NULL
```
- Ben, Okafor, NULL
- Farah, Ahmed, NULL
- Amira, Hassan, NULL
- Dana, Kowalski, NULL
Show answer
Farah, Ahmed, NULL. Only Farah has no hires, so the LEFT JOIN gives NULL for her HireID: Farah, Ahmed, NULL.
- tblCustomer has 6 rows and tblHire has 8. How many rows does SELECT * FROM tblCustomer, tblHire return with no join condition?
Show answer
48. Every customer is paired with every hire: 6 x 8.
- A query using CustomerID from tblCustomer and tblHire gives an 'ambiguous column name' error. What fixes it?
- Rename the table
- Prefix the field with its table or alias, such as c.CustomerID
- Remove the ORDER BY
- Use LEFT JOIN instead
Show answer
Prefix the field with its table or alias, such as c.CustomerID. The database does not know which table's CustomerID you mean.
- Which query type finds bikes that have never been hired?
- tblBike INNER JOIN tblHireItem
- SELECT * FROM tblHireItem
- tblHire INNER JOIN tblCustomer
- tblBike LEFT JOIN tblHireItem, where HireID IS NULL
Show answer
tblBike LEFT JOIN tblHireItem, where HireID IS NULL. A LEFT JOIN keeps every bike; those with no matching hire item have NULL.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (Issue 2, February 2021): Unit 4, Topics 17 to 19
- Pearson: IAL Information Technology scheme of work for Unit 4
Last updated 8 November 2026. Spotted a mistake? Tell us.