Just One More JOIN Play

a dunder_quest game · learn SQL

Just One More JOIN

He knows who did it inside five minutes. Knowing isn't proof. Proof is one question, asked exactly right, and the department's new machine only takes questions in SQL.

Play in your browserThe five cases →

Free. Works on a phone held sideways, or with a keyboard or gamepad. No typing: you build each query from chips.

Just One More JOIN: the name in fat cream and orange letters over a Los Angeles street at night, a rumpled detective in a raincoat and his basset hound under a street lamp.

The trailer

Just One More JOIN trailer thumbnail: the Lieutenant with a finger raised and Mrs. Harrow with her arms folded, under 'Learn SQL' and SELECT name FROM guests WHERE arrived > '21:00'Play the Just One More JOIN trailer

More on the channel: youtube.com/@dunderquest.

How it plays

  1. A letterboxed street at night: Sterling Voss under a lamp, and the caption that he strolls into the gala at 9:50 and hands over his overcoat

    Watch them do it

    Every case opens on the crime. You see who did it and how they covered it, before the Lieutenant has even arrived. So the question is never who. It's what record gives them away.

  2. The Lieutenant's notebook: a question for Records, the tables found so far, chips for each clause, and the SQL they spell underneath

    Write the query

    Walk him round the room. Every record he finds goes into the notebook as a table. Then build the question a clause at a time: what to SELECT, FROM which table, JOINed to what, WHERE.

  3. A green-bar printout of ten guests and their dishes; beside Augusta Harrow, the dish is NULL

    Read what comes back

    Doris in Records reads back exactly what the database returns: the rows, or its own error. A case moves on when your query returns what the question asked for, however you got there.

Never seen SQL? It starts there

A first game opens on a short prologue that tells the story and says what a database is: a table is a file of records, a row is one record, a column is one fact about it, and a query is a question you ask it. Then it types one, and the rows it matches light up.

The prologue: a query typed on a notebook page, SELECT name, arrived FROM guests WHERE arrived > '21:00', beside the guest book as a table with the three matching rows lit The Lieutenant by the hotel's service elevator, a finger raised, telling Mrs. Harrow he has one more join

The cases

Five cases, from your first SELECT to finding the one row that isn't there. Each new clause arrives on a card from the Lieutenant's notebook, with a query and what it returns. These are the cards.

  1. Case 1 · The Hartwell Gala, Bel Air

    Select Company

    SELECT, FROM, WHERE

    Tonight's suspect: Sterling Voss, real-estate developer, at a gala all evening. 3 questions for Records.

    SELECT and FROM
    SELECT name, arrived
    FROM guests

    A table is a file of records: every row is one record, every column is one fact about it. FROM says which table. SELECT says which columns to read back, in the order you list them. SELECT * means all of them.

    WHERE
    SELECT name, arrived
    FROM guests
    WHERE arrived > '21:00'

    WHERE keeps only the rows where the condition is true. Text goes in single quotes and has to match to the letter. Records writes the time the way the Army does: '21:00' is nine at night, so later times sort after earlier ones.

  2. Case 2 · The Palisades Racquet Club

    Out of Order

    LIKE, AND, OR, ORDER BY, LIMIT

    Tonight's suspect: Dinah Bannister, three-time finalist, owner of the Palisades Racquet Club. 4 questions for Records.

    LIKE
    SELECT plate, owner
    FROM cars
    WHERE plate LIKE '%2'

    LIKE matches a pattern instead of the whole text. % stands for any run of characters (even none), and _ for exactly one. 'S%' starts with S. '%2' ends in 2. '%K%' has a K in it anywhere.

    AND, OR
    SELECT plate, owner
    FROM cars
    WHERE color = 'white' OR color = 'red'

    AND keeps a row only when both conditions are true, so it narrows. OR keeps a row when either one is true, so it widens. No car is white AND red. Three are white OR red.

    ORDER BY and LIMIT
    SELECT plate, at
    FROM gate_log
    ORDER BY at
    LIMIT 2

    Unless you say ORDER BY, rows come back in no order you can count on. ORDER BY sorts them, smallest first; add DESC for largest first. LIMIT stops after that many rows. Together they ask for the first, the last, the most.

  3. Case 3 · The House of Brandt, Malibu

    Inner Circle

    JOIN ... ON

    Tonight's suspect: Hugo Brandt, astrologer to the stars, at home in seclusion. 3 questions for Records.

    JOIN ... ON
    SELECT calls.at, phones.owner
    FROM calls
    JOIN phones ON calls.caller = phones.number
    WHERE calls.minutes > 5

    JOIN lays two tables side by side. ON says which rows go together: here a call goes with the phone that made it. Get ON wrong and you match every call with the wrong phone. With two tables in play, write table.column so there's no doubt whose column you mean.

  4. Case 4 · Dr. Sloane's Office, Beverly Hills

    Group Therapy

    COUNT, GROUP BY, HAVING

    Tonight's suspect: Dr. Miriam Sloane, psychiatrist, Beverly Hills. 4 questions for Records.

    COUNT(*)
    SELECT COUNT(*)
    FROM prescriptions
    WHERE drug = 'Calmex'

    COUNT(*) doesn't read the rows back. It counts them and hands you one number. WHERE still chooses which rows get counted. SUM, MIN, MAX and AVG do the same kind of thing to one column.

    GROUP BY
    SELECT drug, COUNT(*)
    FROM prescriptions
    GROUP BY drug

    GROUP BY sorts the rows into piles, one pile for each value, and then the count is taken pile by pile: one row back per pile. Anything you SELECT beside the count has to be something you grouped by.

    HAVING
    SELECT drug, COUNT(*)
    FROM prescriptions
    GROUP BY drug
    HAVING COUNT(*) > 5

    WHERE throws out rows before they're piled up. HAVING throws out whole piles afterward. A count doesn't exist until the piles do, so a test on COUNT(*) belongs in HAVING, never in WHERE.

  5. Case 5 · The Harrow Grand Hotel

    Left for Dead

    LEFT JOIN, IS NULL

    Tonight's suspect: Augusta Harrow, owner of the Harrow Grand Hotel. 4 questions for Records.

    LEFT JOIN
    SELECT detectives.name, motor_pool.car
    FROM detectives
    LEFT JOIN motor_pool ON detectives.desk = motor_pool.desk

    A plain JOIN drops any row that has no partner. LEFT JOIN keeps every row of the left-hand table, the one in FROM. Where there's no partner, the other table's columns come back NULL: nothing there. The department never issued the Lieutenant a car.

    IS NULL
    SELECT detectives.name
    FROM detectives
    LEFT JOIN motor_pool ON detectives.desk = motor_pool.desk
    WHERE motor_pool.car IS NULL

    NULL means there's no value here. It isn't equal to anything, not even to another NULL, so = NULL is never true and finds nothing. Ask with IS NULL, or IS NOT NULL. LEFT JOIN and then IS NULL is how you find what's missing.

It's real SQL

The chips only spell text. What runs is the SQL you can read on the page, through a SQL engine written for the game, and it answers the way SQLite does: the same rows, the same column names, and the same errors, word for word. On every change to the game, a script takes every query that every page of the notebook can spell, about fifteen thousand of them, runs each one through the real SQLite, and fails if the game disagrees on a single row or message.

There is one place the game is stricter than SQLite, on purpose. Ask for a column beside a COUNT(*) without grouping by it, and SQLite will answer with a value from whichever row it likes. PostgreSQL and most other databases refuse, and so does Doris, in PostgreSQL's words. The same script checks that SQLite really would have let it through.

A printout: WHERE COUNT(*) > 2 refused with SQLite's error, misuse of aggregate: COUNT(), and the Lieutenant's note that a test on a count goes in HAVING

SQL was first described in 1974, as SEQUEL, by two researchers at IBM. The game is set that year. The department got its copy early.

Homicide, and the people it calls on

The end of a case, frozen: the Lieutenant thinking, Mrs. Harrow caught

Act II: the paper trail

Designed, not built yet. More cases, and more of the language:

How to play

Just One More JOIN is an homage to a certain rumpled television detective and the mysteries that showed you the murder first. The names, cases, art and music are its own, and it isn't affiliated with or endorsed by anyone who made them.