• Peter.Frissen (1/31/2011)


    Hi, Cool stuff this CTE, but I have a question about the example.

    Suppose I want to return not only the leaf-to-parent relation, but also any subparent-to-parent? For example, b -> c (level 0), b -> d (level 1) and c -> d (level 0)?

    Thanks in advance!

    I think I understand your question, but apologies if not.

    My example CTE starts off with ultimate children (records existing in the "child" column, but not in the "parent" column) and then wanders up the hierarchal tree. You could just as easily work the other way around (select all "parent" records which don't exist in the "child" column) and wander down the tree. In your case, it might even be worth starting off with all "parent" records irrespective of whether they're children or not. It'll increase the size of your CTE, but will allow you to see inherited relationships between parents and children within the middle of the hierarchal tree.

    Hope that helps

    Semper in excretia, suus solum profundum variat