Untitled query #88547

SQL

WITH RECURSIVE subcategories AS (
    -- Base case: direct subcategories
    SELECT page_id, page_title, 1 as level
    FROM categorylinks
    JOIN page ON cl_from = page_id
    WHERE cl_to = 'Companies'
    AND page_namespace = 14

    UNION ALL

    -- Recursive case: subcategories of subcategories
    SELECT p.page_id, p.page_title, s.level + 1
    FROM subcategories s
    JOIN categorylinks cl ON cl_to = REPLACE(s.page_title, '_', ' ')
    JOIN page p ON cl_from = p.page_id
    WHERE p.page_namespace = 14
)
SELECT DISTINCT page_title, level
FROM subcategories
ORDER BY level, page_title;
SQL

Checking query status...
