I've 2 tables representing a similar folder structure like following:
table1
| Id | Name |
| ------------ | ------------ |
| 1 | a |
| 2 | a_b |
| 3 | a_b_c |
| 4 | x |
| 5 | x_z |
| 6 | x_z_y |
table2
parentId | childId |
---|---|
1 | 2 |
2 | 3 |
My input is a_b_c. Is there a way (without cursors) with a single query fetch all parents of input a_b_c? Expected result would be:
Id | Name |
---|---|
3 | a_b_c |
2 | b_c |
1 | -a |
2条答案
按热度按时间wgeznvg71#
Something like this perhaps:
I had to create root rows by doing a UNION, usually the hierarchy table2 should contain both those with and without parents
velaa5lx2#
Using the provided example data (and embellishing on it a little):
This is a classic uses for recursive common table expressions. We're going to start with all the root children, and work our way through:
I've added a variable into the mix which optionally allows you to limit the rows returned to a particular child, if you leave it null it returns all rows.