#034Level 4 of 6 · Scenario 6: The Install List
Everything in the way
Scenario 6 · The Install List
The registrar is about to go back to the curator and wants one sheet rather than three. Conservation, loans and provenance each keep their own book and none of them has a column in common beyond the accession number. What she needs is a single list of open items against anything on the install list, each line saying which department it sits with, so she can walk into the meeting with a number.
Schema · 5 tables
objects
- idpkINTEGER
- titleVARCHAR
- wingVARCHAR
- valuationDOUBLE
picks
- idpkINTEGER
- object_idINTEGER
- wallVARCHAR
- wall_dateDATE
bench
- idpkINTEGER
- object_idINTEGER
- treatmentVARCHAR
- due_backDATE
loans_out
- idpkINTEGER
- object_idINTEGER
- object_noteVARCHAR
- borrowerVARCHAR
- returns_onDATE
holds
- idpkINTEGER
- object_idINTEGER
- raised_byVARCHAR
- raised_onDATE
Toolkit
- SELECT
- Choose which columns come back.
- FROM
- Specify which table to read.
- WHERE
- Filter rows by a condition.
- ORDER BY
- Sort the results by a column.
- INNER JOIN
- Keep only rows that matched in both tables.
- IS NULL
- Test for a missing value. Never use = NULL.
- COUNT(*)
- How many rows there are.
- GROUP BY
- Split the rows into groups first, then aggregate each one.
- UNION
- Stack one result on another. Both sides need the same number of columns, and duplicate rows are removed.
- UNION ALL
- The same, keeping every row, including rows that repeat.
Objective
Return one row per open item held against an object on the install list: its accession number, its title, and the department — 'conservation' for a bench booking, 'loans' for a loan out, 'provenance' for a hold. Sort by accession number, then by department.
Your query
⌘ ⏎
Results
No result yet.