Given a child id, I need to return a query containing all parents of that child as well as their parents till I get to the root parent. For example, given this data:
ID / Parent ID
1 / 0
2 / 1
3 / 2
4 / 0
5 / 3
So if I passed in ID 5 I would like to get a query with the results:
ID / Parent ID
1 / 0
2 / 1
3 / 2
This table does not work with a hierarchyid type so I suspect that this will need to be done with a CTE, but have no clue how. If it can be done in an SQL query / proc, any help would be appreciated.
Thanks