sql - How to select parent ids -

i have table such structure.

elementid |  parentid ------------------- 1         | null 2         | 1 3         | 2 4         | 3 

let current element has id 4. want select parent ids. result should be: 3, 2, 1

how can it? db mssql

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

you can use this:

with hierachy(elementid, parentid, level) (     select elementid, parentid, 0 level     table t     t.elementid = x -- insert parameter here     union     select t.elementid, t.parentid, th.level + 1     table t     inner join hierachy th     on t.parentid = th.elementid ) select elementid, parentid hierachy level > 0