Give root categories readable labels
Your task
Return category ID and display_name. A child’s label should be its parent’s name, then ' / ', then its own name. A root’s label should be its own name. Sort by category ID.
When concatenation meets NULL, the whole result can become NULL. Choose a fallback for the missing parent.
Sample data
categories| id | name | parent_id |
|---|---|---|
| 1 | Music | NULL |
| 2 | Jazz | 1 |
| 3 | Indie | 1 |
| 4 | Bebop | 2 |
| 5 | Cool Jazz | 2 |
| 6 | Merch | NULL |
| 7 | Shirts | 6 |
Expected output
| id | display_name |
|---|---|
| 1 | Music |
| 2 | Music / Jazz |
| 3 | Music / Indie |
| 4 | Jazz / Bebop |
| 5 | Jazz / Cool Jazz |
| 6 | Merch |
| 7 | Merch / Shirts |
Show a hint
Join categories to themselves with a LEFT JOIN and use COALESCE on the full concatenation.
Expressions and missing values guide
Calculate a value for each row
Expressions can combine columns, constants, and functions. This query estimates how much stock is worth at the listed price. AS gives the calculation a name in the result.
SELECT title, price * stock AS stock_value
FROM albums;Build a category from conditions
CASE tests branches from top to bottom. Put the most specific test first: zero would also satisfy stock < 5. ELSE catches the remaining rows.
SELECT title,
CASE
WHEN stock = 0 THEN 'out of stock'
WHEN stock < 5 THEN 'low stock'
ELSE 'stocked'
END AS availability
FROM albums;Account for missing values
NULL represents an unknown or missing value. Compare it with IS NULL rather than equality. NULLIF can also turn a known sentinel into NULL. AVG ignores NULL, so these two averages use different sets of albums.
SELECT AVG(stock) AS all_albums,
AVG(NULLIF(stock, 0)) AS in_stock_only
FROM albums;Worked solution
SELECT c.id, COALESCE(p.name || ' / ' || c.name, c.name) AS display_name FROM categories c LEFT JOIN categories p ON p.id = c.parent_id ORDER BY c.id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
