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.

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

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.HireDate

Result (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 | 3

In 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.Model

Result (4 rows):

HireID | Model      | Cost
-------+------------+------
105    | Commuter   | 20.00
105    | Ridge Pro  | 44.00
106    | Trail 500  | 72.00
107    | City Glide | 12.50

LEFT 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 NULL

Result (1 row):

FirstName | Surname | HireID
----------+---------+-------
Farah     | Ahmed   | NULL

In 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

Worked example

List every hire by Amira Hassan with the model of each bike, earliest first.

  1. ```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 ```
  2. 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.

Key idea: INNER JOIN ... ON fk = pk returns matching rows; go through link tables for M:M; LEFT JOIN plus IS NULL finds records with no matches; always give a join condition and qualify shared field names.

Check yourself

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.