Geek asks and answers

How to choose parent identifiers

I have a table with such a structure.

ElementId |  ParentId
-------------------
1         | NULL
2         | 1
3         | 2
4         | 3

Suppose the current item has an identifier of 4. I want to select all the parent IDs. The result should be: 3, 2, 1

How can i do this? DB - MSSQL

+2
source share
3 answers

You can use recursive queries for this: http://msdn.microsoft.com/en-us/library/aa175801(SQL.80).aspx

You can use it as follows:

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
+2
source

I think it would be easier to do the following:

while parent != NULL
    get parent of current element

I cannot think of any way to do this in simple SQL that will not cause problems with large databases.

+1
source

sql:

select ParentId from myTable Desc

mysql... Desc ( )

-1
source

More articles:


All Articles