MySQL recursive query filtering branch

Viewed 34

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.

0 Answers
Related