Good day, dear forum users! I ask you for help. Stalled on the following:

Initial data: There is an anytab table with id, descr, parentid, isgroup fields. The isgroup = 1 field for groups. The nesting level is different.

Task: Get id and descr values

Condition: Regardless of nesting, get all descendants by the parameter @parent (@parent = parentid) except for those where isgroup = 1, that is, except for groups.

System: MS SQL Server 2008 R2

Thank you in advance!

    2 answers 2

    WITH x AS ( SELECT @parentid AS id UNION ALL SELECT at.id FROM anytab AS at JOIN x ON at.parentid = x.id WHERE at.isgroup = 1 ) SELECT at.id, at.descr FROM anytab AS at JOIN x ON at.parentid = x.id WHERE isgroup <> 1
    • Thanks for the decision! It helped with minor changes: WITH x AS (SELECT at.id AS id id FROM anytab AS at WHERE at.id = @parentid UNION ALL SELECT at.id FROM anytab AS at JOIN x ON at.parentid = x.id WHERE at.isgroup = 1) SELECT at.id, at.descr FROM anytab AS at JOIN x ON at.parentid = x.id WHERE isgroup <> 1 - Freezze

    Solution to the forehead:

    1. Write a function, for example, isAncestorOf(@parent_id, @child_id) , which will while while alternately pull parents for a node, until it reaches @parent_id or null .
    2. Using this function, select all nodes that have isgroup <> 1 and the result isAncestorOf is positive.

    In addition, you can try to use recursion, which is supported in MSSQL since 2005 and bypass the desired subtree. In practice, I have not tried, I can not say more specifically.