MySQL Recursive CTE to retrieve all Attached Nodes of a Table

Question

I have a recursive structure with the following two tables:

1. tree represents a hierarchy of nodes where the top node has parent = NULL.
2. the items can be attached to any of the tree nodes, anywhere in the hierarchy.

CREATE TABLE IF NOT EXISTS tree (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  parent int UNSIGNED,
  name VARCHAR(50) NOT NULL,
  FOREIGN KEY (parent) REFERENCES tree(id)
);

CREATE TABLE IF NOT EXISTS items (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  tree_id INT UNSIGNED NOT NULL,
  FOREIGN KEY (tree_id) REFERENCES tree(id)
);

Example:

.
Tree-1
  │ └── Item-1
  │ └── Item-2
  │
  └── Tree-2
        │ └── Item-3
        │
        └── Tree-3
                └── Item-4

I want to pick a start Tree node and return all the items at that level or below. So for example, if I start with Tree-1 I should get back items 1-4, with Tree-2 I should get items 3 and 4. If I start at Tree-3 I get only item 4.

First Attempt

Simple recursive CTE to traverse down the tree:

WITH RECURSIVE tree_hierarchy AS (
    SELECT id, name, parent
        FROM tree
        WHERE id = 2
    UNION ALL
        SELECT tree.id, tree.name, tree.parent
            FROM tree
            INNER JOIN tree_hierarchy ON spaces.parent = tree_hierarchy.id
)
SELECT * FROM tree_hierarchy;

I'm trying to write a recursive CTE query but getting nowhere. Can anyone help?

Answer 1

You're almost there. You have the recursive CTE that produces the set of descendant tree nodes. You just need to join those to any items that reference those tree nodes.

WITH RECURSIVE tree_hierarchy AS ( 
    SELECT id, name, parent
        FROM tree
        WHERE id = 2
    UNION ALL
        SELECT tree.id, tree.name, tree.parent
            FROM tree
            INNER JOIN tree_hierarchy ON tree.parent = tree_hierarchy.id
)
SELECT * FROM tree_hierarchy
LEFT OUTER JOIN items ON items.tree_id = tree_hierarchy.id;