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.
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.
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.
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 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.
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.
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.
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.
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.
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.
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
Lieutenant ColonnaHomicide. A raincoat, a cigar, a pencil he can never find
Nullthe Lieutenant's basset hound. He never got around to giving him a name
Doris, Records Divisionruns the department's new computer, and reads back exactly what it says
Sterling Vossreal-estate developer, at a gala all evening (a suspect)
Dinah Bannisterthree-time finalist, owner of the Palisades Racquet Club (a suspect)
Hugo Brandtastrologer to the stars, at home in seclusion (a suspect)
Dr. Miriam Sloanepsychiatrist, Beverly Hills (a suspect)
Augusta Harrowowner of the Harrow Grand Hotel (a suspect)
Act II: the paper trail
Designed, not built yet. More cases, and more of the language:
subqueriesa question inside a question: everyone who paid more than the average
WITHgive a query a name, then ask it something
window functionsthe row before this one: who used the key card just ahead of the victim
UPDATE and DELETEwhat happens to a ledger when somebody forgets the WHERE
transactionsthe transfer that was started and never committed
How to play
KeyboardArrows or A/D to walk, E to talk or to look at a record. In the notebook, arrows move between chips, Space picks one, Enter calls Records.
PhoneHold it sideways. Arrows on the left, TALK on the right; in the notebook, tap the chips.
GamepadD-pad or stick to walk, X to talk, A to pick a chip, Y to call Records.
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.