Cracking SQL Hierarchies: How I Built a Query to Track Multi-Level Product Parts

Ever tried building a query to calculate nested component quantities for a product? It’s like unpacking Russian nesting dolls. Recently, a user in the r/SQL subreddit asked for help solving this exact problem—here’s the breakdown I’d share with a friend over coffee. (And yes, recursive CTEs save the day.)

## Real-World Meet-The-Table Moment
Picture a manufacturing database: one product depends on five subassemblies, each depending on three more. You need to know *exactly* how many of Part G-1245 are required to build a single Product X-3000. The table structure looks like this:

– `PRTNO`: The part being used
– `HighLevelAssembly`: The product it’s part of
– `QuantityPerArticle`: How many parts needed in this step

The tricky part? Relationships can go multiple layers deep. Product family trees, not just flat lists.

## Recursive CTE to the Rescue
After dozens of failed UNION approaches, the solution came via recursive Common Table Expressions. Think of it as creating a temporary family map for your query:

WITH RECURSIVE AssemblyLine AS (
  -- Starting point: Our main product's top components
  SELECT 
    PRTNO,
    HighLevelAssembly AS ParentAssembly,
    QuantityPerArticle,
    CAST(1 AS INT) AS TotalRequired
  FROM T1
  WHERE HighLevelAssembly = 'YOUR_PRODUCT_HERE'
  
  UNION ALL
  
  -- Recursively find required parts
  SELECT 
    t.PRTNO,
    t.HighLevelAssembly,
    t.QuantityPerArticle,
    a.TotalRequired * t.QuantityPerArticle
  FROM T1 t
  INNER JOIN AssemblyLine a
  ON t.HighLevelAssembly = a.PRTNO
)
-- Sum quantities for each part
SELECT 
  PRTNO,
  SUM(TotalRequired) AS GrandTotal
FROM AssemblyLine
GROUP BY PRTNO;

## Why This Works
1. **Base Case**: Starts with parts directly in the product tree
2. **Recursion**: Jumps down through successively deeper components
3. **Multiplication Magic**: Tracks how many instances needed at each layer
4. **Summation**: Handles parts appearing in multiple branches (think: screws used in different panels)

## Watch the Edge Cases
This query assumes no circular references (“Part A needs Part A”), but we all have that one messy dataset. Add tier tracking or path-tracking columns if you see infinite loops:

..., CAST(PRTNO AS TEXT) AS Path
...
-- In recursion step
WHERE a.Path NOT LIKE '%' || t.PRTNO || '%'

## Key Takeaway
Never manually count parts through spreadsheets again. This SQL pattern handles everything from toasters to ship components. Just change what product number you’re starting from, and voilà!

*Funny confession*: I spent three hours staring at the problem before remembering recursion exists. Keep this in your SQL cheat sheet—it’ll save you someday.