Bits of .NET

🌳 Org Charts, Categories, Comment Threads

Self-joins are messy. Recursive CTEs query parent-child relationships elegantly. Get entire organization tree in one query.

📝 Employee Hierarchy

WITH RECURSIVE org_tree AS (
  -- Anchor: top-level managers
  SELECT id, name, manager_id, 0 as level, name as path
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive: employees under each manager
  SELECT e.id, e.name, e.manager_id, ot.level + 1,
         ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, 
       REPEAT('  ', level) || name as indented_name,
       level,
       path
FROM org_tree
ORDER BY path;

🎯 Category Breadcrumbs

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, name as breadcrumb
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id,
         ct.breadcrumb || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, breadcrumb
FROM category_tree
ORDER BY breadcrumb;

-- Results:
-- Electronics > Computers > Laptops
-- Electronics > Computers > Desktops
-- Electronics > Phones > Smartphones
-- Clothing > Men > Shirts

✅ Find All Subordinates

• Get team under specific manager: WHERE id = 123
• Limit recursion depth: WHERE level < 5
• Count subordinates: SELECT manager, COUNT(*) GROUP BY manager_id
• Detect cycles: Use cycle detection (PostgreSQL, SQL Server)