I have a recursive structure with the following two tables:
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.
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?
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;