How to select parent ids

Question

I have table with such structure.

ElementId |  ParentId
-------------------
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

Answer 1

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

Answer 2

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.

Answer 3

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