Ride Me Cycles' costing sheet needed every part behind a finished bike, not just the sub-assemblies bolted onto it on the build sheet. A recursive query answers that in one pass, at any depth, without a fixed number of joins.
BlackTor Group LtdDartmoor, UK
A note on this page
A reference, not a client write-up.
Illustrative, per the note on the main SQL page: not real client work. It sits alongside the Bay Booking and Reorder Point pages as a deeper dive on one query pattern that comes up often enough to be worth its own write-up.
The issue
A bike is built from parts built from parts.
Ride Me Cycles' Parts table records each bike's build the same way the workshop thinks about it: a finished bike is made of sub-assemblies (a wheel, a drivetrain, a cockpit), each of those is made of smaller parts, and some of those break down further still. In the table this is one self-referencing column, parent_part_id, pointing each part at whatever it's bolted to.
Costing a bike meant walking all the way down that chain to the raw parts at the bottom, since that's where the actual supplier cost sits. The query in use before this joined Parts to itself a fixed number of times, three or four levels deep. It worked until a new sub-assembly introduced a fifth level, at which point the query kept running, produced a plausible-looking total, and simply stopped one level short with no error to say so.
Recursive CTE
BikePartsExplosion: walk the parts tree to whatever depth it actually has
-- BikePartsExplosion: recursively walk a bike's bill of materials to any
-- depth, accumulating the quantity actually required at the TOP level (not
-- just the quantity relative to its immediate parent), so the result can be
-- costed directly without a further pass over it.
WITH RECURSIVE PartsExplosion AS (
-- Anchor member: the finished bike itself, one unit of it, at depth 0.
SELECT
part_id,
part_name,
unit_cost,
1 AS quantity_required,
0 AS bom_depth
FROM Parts
WHERE part_id = ? -- e.g. the 'Trailblazer 29' part_id
UNION ALL
-- Recursive member: for every part whose parent is already in the
-- explosion, add it in, multiplying its OWN quantity_per_parent by the
-- quantity the PARENT needed -- so a part two levels down correctly
-- inherits both multipliers, not just its own.
SELECT
p.part_id,
p.part_name,
p.unit_cost,
pe.quantity_required * p.quantity_per_parent,
pe.bom_depth + 1
FROM Parts AS p
INNER JOIN PartsExplosion AS pe ON pe.part_id = p.parent_part_id
)
SELECT
part_name,
bom_depth,
SUM(quantity_required) AS total_quantity_required,
SUM(quantity_required) * unit_cost AS extended_cost
FROM PartsExplosion
GROUP BY part_id, part_name, bom_depth, unit_cost
ORDER BY bom_depth, part_name;
The anchor member seeds the recursion with the bike itself. The recursive member then repeatedly joins Parts back onto the rows the CTE has already produced, picking up one more generation of children each pass, and stops on its own the moment a pass finds no more matching children: there's no hard-coded depth limit to fall short of, so a sixth or seventh level added to the product range next year needs no change to this query at all.
The running multiplication (pe.quantity_required * p.quantity_per_parent) is what keeps the totals correct at every depth: a part needed twice per sub-assembly, in a sub-assembly needed twice per bike, correctly reports needing four, not two.
For speed: parent_part_id needs an index (CREATE INDEX idx_parts_parent ON Parts(parent_part_id);), the same principle as the index before you optimise the query text note on the main SQL page. Without it, every recursive step re-scans the whole Parts table looking for children; with it, each step goes straight to the rows it needs.
Result
One query, any depth, no manual joins to maintain.
A costing sheet that used to need a fixed-depth query rebuilt by hand whenever the product range grew a new level now just runs. New sub-assemblies slot into the existing Parts table and the same query picks them up automatically.
