[RESOLVED] Finding all Orgs that are not descendants

Derokorian

Ok so what I'm trying to do is for form there is an option to set a Parent Organization for the current Organization. But I have to remove descendants of any level (could be an immediate child, or a 28th generation child etc) from the list and I'm not sure if I can do it in one query or not. The table structure (simplified) is: Organizations: id int PK, name, ParentID (points to PK, nullable) Obviously its easy to remove immediate children (WHERE ParentID <> $currentID) but how to also ignore each child's children, and their children, and.... you get the idea.

Any thoughts? Do you need more info or is there a sample I've not been able to find googling?

Weedpacket

Er ... I dashed this off and then I realised it would probably get the opposite of what you want....

With org_tree(id) as
	(Select id
		From Organizations
		Where id=$currentID)
	Union All
	(Select o.id
		From Organizations o, org_tree t
		Where o.ParentID = t.id)
Select id from org_tree

Derokorian

Yes and No: Just had to make the select id from org_tree as a subquery, and the outer uses where not in. Works perfectly in the end after combining with another response elsewhere:

public GetDescendants(OrgID,ReturnNonDescendants)
        {
            strSQL = "WITH cteRecursion AS (
                            SELECT OrganizationId, 1 AS Level
                                FROM Organization
                                WHERE OrganizationId = @PassedID
                            UNION ALL
                            SELECT O.OrganizationId, c.Level+1
                                FROM Organization O
                                    INNER JOIN cteRecursion c
                                        ON O.ParentOrganizationId = c.OrganizationId
                        )
                        SELECT * FROM Organization WHERE ParentOrganizationId ";
            if( ReturnNonDescendants )
                strSQL .= "NOT ";
            strSQL .= "IN (
	                        SELECT OrganizationId
                            FROM cteRecursion
                            )";

Derokorian

Ran into the reverse situation today, where I needed to get all ancestors so this is what I came up with:

WITH cteRecursion AS (
    SELECT ParentOrganizationId, 1 AS Level
        FROM Organization
        WHERE OrganizationId = @PassID
    UNION ALL
    SELECT O.ParentOrganizationId, c.Level+1
        FROM Organization O
            INNER JOIN cteRecursion c
                ON O.OrganizationId = c.ParentOrganizationId
)
SELECT * FROM Organization o
INNER JOIN cteRecursion c ON o.OrganizationId = c.ParentOrganizationId