I've have a tree parent-child structure in MySQL. Every node has a lot of attributes (not in the example). I'd like to extract every branch of my tree where dome node have an attribute that match my query.
For example:
------------------------
|id|parent |description |
------------------------
| 1|NULL |First root |
| 2|NULL |Second root |
| 3|1 |First child |
| 4|1 |Second child|
| 5|1 |Third child |
| 6|2 |First child |
| 7|2 |Second child|
| 8|6 |First child |
| 9|6 |Second child|
|10|6 |Third child |
|11|5 |First child |
Making a query somewhat similar to:
WHERE description LIKE "Third%"
I'd like to have back:
------------------------
|id|parent |description |
------------------------
| 1|NULL |First root |
| 2|NULL |Second root |
| 5|1 |Third child |
| 6|2 |First child |
| 10|6 |Third child |
Any ideas?
I have a query like this:
WITH RECURSIVE tmp (id,parent,description,parent_name, parent_description) AS (
SELECT id,parent,description,NULL,NULL
FROM table
WHERE parent IS NULL
UNION ALL
SELECT table.*, tmp.parent,tmp.description
FROM tmp
JOIN table ON table.parent=tmp.id
WHERE table.description LIKE "Third%"
)
SELECT * FROM tmp;
But it doesn't extract what I need. I'd like to have the whole structure where at least one node matches the condition.