Joining Three Tables and More

The Marquee's flagship question has been waiting since Chapter 1: who is coming to Night Bus on Friday, and in which seats? Look at where the pieces live and you'll see why it had to wait — the names are in customers, the seats in bookings, the showtime in screenings, the title in films. Four tables, one question.

The good news is that this page contains nothing new. JOINs chain: each JOIN adds one more table to the working answer, using the same "foreign key equals primary key" move you already own. A four-table query is three well-lit steps, not a leap — and the technique of taking those steps one at a time is what this page actually teaches.

One JOIN at a Time

Never write a big query in one breath. Build it, and say out loud what you have after each step. Step one is last page's territory — bookings with their customers' names:

SELECT c.name, b.seat
FROM bookings b
JOIN customers c ON c.customer_id = b.customer_id;

After this step, each row is a booking wearing its customer's name: "Anna, seat G7". No showtime yet, no film. Step two follows the booking's other pointer, screening_id, into the screenings table:

SELECT c.name, s.starts_at, s.screen, b.seat
FROM bookings b
JOIN customers c ON c.customer_id = b.customer_id
JOIN screenings s ON s.screening_id = b.screening_id;

Now each row reads "Anna, Friday 20:00, screen 1, seat G7" — a booking with its person and its showtime, still missing only the film's name. The screening row carries film_id, so step three follows that last pointer into films, and while we're there, filters down to the show Lora asked about:

SELECT c.name, f.title, s.starts_at, b.seat
FROM bookings b
JOIN customers c ON c.customer_id = b.customer_id
JOIN screenings s ON s.screening_id = b.screening_id
JOIN films f ON f.film_id = s.film_id
WHERE f.title = 'Night Bus'
  AND s.starts_at = '2026-05-15 20:00';

Four tables, and the query still reads top to bottom like a story: start from the bookings, name the person, name the show, name the film, keep Friday's Night Bus. Each JOIN line was one repetition of a move you learned two pages ago.

The Chain Follows the Schema

Here is the part that turns technique into confidence: you never have to invent a JOIN path, because the schema already drew it. The arrows from Chapter 2's diagram — bookings point at customers and screenings, screenings point at films — are exactly the roads a query may travel. Every ON clause in the flagship query is one of those arrows, written as an equality.

This works in both directions, and the reverse direction is your day-one skill at any job: handed someone else's five-table query, put the schema diagram beside it and trace each ON clause along an arrow. The query stops being a wall of syntax and becomes a route on a map you can follow with a finger.

Does JOIN Order Matter?

A natural worry: with three JOINs, did we have to write customers before screenings? For a chain of inner joins, no — the rows that come back are the same whichever order you list the tables in, as long as every ON clause still connects to something already introduced. And one honest sentence about what's under the hood: the database's query planner reorders joins as it sees fit anyway, choosing whatever sequence it computes to be fastest — Chapter 8 pays that sentence off properly.

So the order you write is for people, not for the machine. Start from the table your question is about, and add the others in the order that tells the story most plainly. The engine will do what it wants; your reader will thank you for the narrative.

Keeping It Legible

At four tables, style stops being taste and becomes survival, so name the three habits the flagship query quietly used. Alias every table, and use the aliases everywhere. Put each JOIN on its own line, with its ON immediately after it on the same line — one line, one arrow. And qualify every column in the SELECT list, so six months from now nobody has to guess which table starts_at came from. Shown, not preached: scroll back up and you'll find all three in every step.

One last reassurance, because beginners often read a long FROM clause as a symptom of something wrong. More JOINs do not mean worse design — the schema was built to be joined, and a five-table query over a clean schema beats a no-join query over one bloated table every time. The bloated table is where data rots; the joins are where it comes back together, on demand, correct.