TSQL to get all rows from a recursive table

Question

This is a follow up of the question: SQL Server to show a data tree in a specific format

Now we have a second table that is related to the first one on the "leaf" rows:

table1:

   Itemid   Itemname    Itemfatherid
    itemA    theitemA    null
    itemB    theitemB    null
    itemC    theitemC    itemA
    itemD    theitemD    itemA
    itemE    theitemE    itemC
    itemF    theitemF    itemE
    itemG    theitemG    itemD

In this table itemF and itemG would be leafs (they dont have any children)

table2:

   Itemid   RelItemid
    item1    itemF
    item2    itemF
    item3    itemG

The visual relation would be like this:

-itemA
    -itemC
        -itemE
            itemF (item1, item2)
    -itemD
        itemG (item3)
itemB

And we need to get all the rows from the 2nd table that are related to the first table filtering it by an especified node.

Examples:

Filtering by node itemA: item1, item2, item3
Filtering by node itemE: item1, item2
Filtering by node itemD: item3
Filtering by node itemG: item3
Filtering by node itemB:

Sorry for the long post, its in order to cover any holes...thanks

Related: http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree — Tomalak, Mar 14 '12 at 15:29

score 0 · Accepted Answer · answered Jun 07 '12 at 15:26

0

In the end we solved it looping throu the results and doing sqls to get related rows from the second table...not the desired solution but the one feasible

answered Jun 07 '12 at 15:26

VSP

2,367
8
38
59

score 0 · Answer 2 · answered Mar 23 '12 at 14:23

0

i think that Recursive Queries Using Common Table Expressions! may use full to you

answered Mar 23 '12 at 14:23

Kunal Shah

148
1
6

In the related question we used CTEs the problem is that the resulting query is so complex already that we dont see how to combine it with this new condition – VSP Jun 07 '12 at 15:28

TSQL to get all rows from a recursive table

2 Answers2