AdvancedSubqueries & EXISTS

Recursive subquery: find all ancestors of a category

The query

SQL

WITH RECURSIVE ancestors AS (
  -- Start with a specific category
  SELECT cat_id, name, parent_id, 0 AS depth
  FROM categories
  WHERE cat_id = 15 -- target leaf category

  UNION ALL

  -- Walk up the tree
  SELECT c.cat_id, c.name, c.parent_id, a.depth - 1
  FROM categories c
  JOIN ancestors a ON c.cat_id = a.parent_id
)
SELECT
  name,
  depth,
  CASE WHEN depth = 0 THEN '← Target' ELSE '' END AS note
FROM ancestors
ORDER BY depth;

Tested against PostgreSQL 16

Note

“Upward recursion (child to parent). depth goes negative since we go up. Great for breadcrumb navigation.

Tables referenced

categories3 cols