How to select parent ids
I have table with such structure.
ElementId | Par开发者_如何学PythonentId
-------------------
1 | NULL
2 | 1
3 | 2
4 | 3
Let say current element has Id 4. I want to select all parent ids. Result should be: 3, 2, 1
How I can do it? DB is MSSQL
You can use recursive queries for this: http://msdn.microsoft.com/en-us/library/aa175801(SQL.80).aspx
You can use it like this:
with Hierachy(ElementID, ParentID, Level) as (
select ElementID, ParentID, 0 as Level
from table t
where t.ElementID = X -- insert parameter here
union all
select t.ElementID, t.ParentID, th.Level + 1
from table t
inner join Hierachy th
on t.ParentId = th.ElementID
)
select ElementID, ParentID
from Hierachy
where Level > 0
I think it might be easiest to do the following:
while parent != NULL
get parent of current element
I can't think of any way of doing this in plain SQL that wouldn't cause issues on larger databases.
if you want pure sql try:
select ParentId from myTable Desc
that would work in mysql... you might need to modify the Desc (sort in descending order) part
精彩评论