sql - Retrieve descendant (path) of tree base of multiple values -

i have self reference table store hierarchical values in order show them in treeview or on ,according james crowley article (tree structures in asp.net , sql server)

our table this:  id  parentid      name        depth    lineage 1   null        root node       0       /1/ 2   1           child         1       /1/2/ 3   1           child b         1       /1/3/ 4   1           child c         1       /1/4/ 5   2           child d         2       /1/2/5/ 

to path of node (for example id=5) suggest following query against table

select * dftree (select lineage        dftree        id = 5) lineage + '%' 

result :

id  parentid      name        depth    lineage 1   null        root node       0       /1/ 2   1           child         1       /1/2/ 5   2           child d         2       /1/2/5/ 

and acceptable

but how have result set when there multiple ids want have path ? therefore example in above example instead of id=5 pass multiple values :

select * dftree (select lineage        dftree        id in (5,6,8,9)) lineage + '%' 

but above statement make no sense , invalid sql server expression
how solve problem ?
in advance

this query...

select distinct t2.*      (         select lineage         dftree         id in (4, 5)     ) t1     join dftree t2     on         t1.lineage t2.lineage + '%' 

...returns following result on test data:

id   parentid   name        depth   lineage 1    null       root node   0       /1/ 2    1          child     1       /1/2/ 4    1          child c     1       /1/4/ 5    2          child d     2       /1/2/5/ 

as can see, paths "merged" - example, path component id=1 belongs both path: /1/4/ , path: /1/2/5/, yet exists in result set once.

---

one other hand, if do need distinguish between different paths, you'd need this:

select t2.*, t1.id leafid      (         select id, lineage         dftree         id in (4, 5)     ) t1     join dftree t2     on         t1.lineage t2.lineage + '%' 

result:

id   parentid   name        depth   lineage    leafid 1    null       root node   0       /1/        4 4    1          child c     1       /1/4/      4 1    null       root node   0       /1/        5 2    1          child     1       /1/2/      5 5    2          child d     2       /1/2/5/    5 

in case, each path identified leaf. assumes there no diamond-shaped dependencies (i.e. real tree , not dag); if there are, you'd need use t1.lineage instead of t1.id identify path.