FREE AI-Enhanced Online MariaDB Compiler

Untitled*

You must execute the code again to save those values in the database.

WITH RECURSIVE MyTree AS (
    SELECT NULL AS ParentCategoryId, 'A' AS ChildCategoryId UNION ALL
    SELECT 'A', 'C' UNION ALL
    SELECT 'A', 'B' UNION ALL
    SELECT 'A', 'D' UNION ALL
    SELECT 'B', 'E' UNION ALL
    SELECT 'E', 'C' UNION ALL
    SELECT 'D', 'C'
),
CTE AS (
    -- Anchor member: select leaf nodes
    SELECT 
        ChildCategoryId AS Node,
        ParentCategoryId AS Parent,
        CAST(ChildCategoryId AS CHAR(100)) AS NodePath,
        1 AS Level
    FROM MyTree
    WHERE ChildCategoryId NOT IN (SELECT DISTINCT ParentCategoryId FROM MyTree WHERE ParentCategoryId IS NOT NULL)

UNION ALL

-- Recursive member: join to previous results and extend the path
    SELECT 
        CTE.Node,
        mt.ParentCategoryId AS Parent,
        CONCAT(mt.ChildCategoryId, ' > ', CTE.NodePath) AS NodePath,
        Level + 1 AS Level
    FROM CTE
    JOIN MyTree mt ON CTE.Parent = mt.ChildCategoryId
)
-- Final SELECT for the recursive CTE to return results without NULL Parents
SELECT DISTINCT
    TRIM(TRAILING ' > ' FROM NodePath) AS 'FullPath'
FROM CTE
WHERE Parent IS NULL
ORDER BY Level DESC;

FullPath
A > B > E > C
A > D > C
A > C