How Far Does the Failed Supply Line Reach?

Supply node 1 feeds nodes 2 and 3. Those nodes feed other equipment. When the supply fails, Botie needs to follow the connections far enough to identify the affected equipment. A single join only follows one connection.

We could write another join for every level, but we do not know the depth of every installation in advance. A recursive query applies the same step to the rows discovered by the previous step. We also need a stopping rule, because a bad record can point back to equipment we already visited.

Seed a starting node, expand its outgoing links, and keep the shortest discovered depth for each reached node.

[
  {
    "node_id": 1,
    "depth": 0
  },
  {
    "node_id": 2,
    "depth": 1
  },
  {
    "node_id": 3,
    "depth": 1
  },
  {
    "node_id": 4,
    "depth": 2
  }
]

An edge row stores parent_id and child_id. The pair says which node supplies another node; it does not contain all descendants. A graph can have several parents for the same child, as node 4 does here.

Decide whether the question wants paths, distinct reached nodes, or quantities propagated along paths. Removing repeated node IDs is suitable for our affected-equipment list, but could destroy useful information in a parts-quantity calculation.

The output grain determines whether two routes to node 4 should remain separate.

The anchor SELECT creates node 1 at depth 0. Its path text is /1/. It runs without reading the recursive result. The recursive term then receives discovered rows and joins their node IDs to the edge table’s parent_id.

UNION ALL preserves newly discovered paths. We increment depth and append each child ID so later expansion can inspect the route already taken.

The anchor answers where to begin. The recursive term answers how to take one more step.

The query below expands rows only while their current depth is below 3. It therefore includes discoveries at depth 3, then stops expanding them. The path check separately prevents revisiting a node on the current route.

After traversal, grouping by node_id and taking MIN(depth) can produce one reached-node row with the shortest depth among the paths explored. First inspect the paths themselves. The final ORDER BY controls display order; recursive processing order is not the report’s order.

The cap defines a bounded report. It does not prove that no equipment exists beyond the cap.

CREATE TABLE edges(parent_id INTEGER NOT NULL, child_id INTEGER NOT NULL);
INSERT INTO edges VALUES (1,2),(1,3),(2,4),(3,4);

WITH RECURSIVE walk(node_id, depth, path) AS (
  SELECT 1, 0, '/1/'
  UNION ALL
  SELECT e.child_id, w.depth + 1, w.path || e.child_id || '/'
  FROM walk AS w JOIN edges AS e ON e.parent_id = w.node_id
  WHERE w.depth < 3
    AND instr(w.path, '/' || e.child_id || '/') = 0
)
SELECT node_id, depth, path FROM walk ORDER BY depth, node_id, path;

Suppose a mistaken edge points from node 4 back to node 1. Without a guard, repeated UNION ALL expansion can revisit the same equipment indefinitely. The slash-delimited path lets instr search for the complete integer ID rather than confusing node 1 with node 11.

Changing UNION ALL to UNION is not a universal cycle repair. If the recursive row contains increasing depth or changing path text, each new row can still be distinct. Deduplication compares whole rows.

Our integer-ID path representation is a teaching choice. Arbitrary text identifiers need a representation whose delimiters cannot be confused with the identifier itself.

A path guard prevents one route from looping. It does not prevent a graph with many branches from containing a very large number of simple paths. A maximum depth, execution budget and an operational row limit can still be necessary.

If the limit is reached, label the result as bounded or incomplete. Quietly treating a truncated report as the full dependency closure could leave affected equipment out of a shutdown plan.

The workshop caps depth and total expanded rows to keep the demonstration responsive. Those limits are not a proof that a real installation is small.

Temporarily select node_id, depth and path from walk to see why a node was reached. If node 4 is missing, check the edge direction. If it appears twice, decide whether you forgot the final node-level summary or whether the task actually needs both routes.

Recursive CTE syntax and built-in cycle facilities differ between engines. This example uses SQLite-compatible string paths rather than assuming another database’s SEARCH or CYCLE syntax.

A final MIN(depth) is a report over discovered paths. It does not repair an incorrect edge direction or missing source records.