Prompt Almanac

Create advanced Works with: Any AI

Recursive Queries for Hierarchies and Org Charts

Few shot

Query trees and nested relationships correctly.

Open in ChatGPTOpen in Claude

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

Rate this prompt

Loading ratings…

Teach me to query hierarchies with recursion. Dialect: [SQL_DIALECT]. Hierarchy: [HIERARCHY_DESCRIPTION] (such as employees and managers, categories, or bills of materials). Table definition: [TABLE_DEFINITION]. Questions to answer: [QUESTIONS]. Possible data problems (cycles, orphans): [DATA_ISSUES].

Learn the pattern from this example on an employees table (id, name, manager_id):
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth, name AS path FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1, c.path || ' > ' || e.name
  FROM employees e JOIN chain c ON e.manager_id = c.id
  WHERE c.depth < 10
)
SELECT * FROM chain ORDER BY path;
Explanation: the first SELECT is the anchor (the root); the second joins each row to its parent row from the previous step; UNION ALL accumulates; the depth limit protects against cycles.

For my table, write queries for: all descendants of a node, the path from a node to the root, the depth of each node, rolled-up totals per subtree, and the number of levels. Add checks for orphans and cycles that I can run first, notes on dialect differences (keyword RECURSIVE or not, string concatenation, cycle detection clauses), and a comparison with closure tables, nested sets, and materialized paths. Include sample data and the expected output.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Related prompts