SQL SELECT to find cyclic references in father-ID-organized tree?

Question

"Fun" with cyclic references:

Suppose I have a table ELEMENTS which contain a hierarchy of elements, modeled by a father ID.

The father ID field is null for the root.

All other records have a non-null father id with the (autosequenced) primary key (ID) of the father element.

For example, using

SELECT *
FROM Elements
WHERE FATHER_ID not in (SELECT ID FROM Elements)

I can find all elements that have invalid father references (FATHER_ID is not a foreign key, let's assume that in this example).

But how can I find elements that do have a valid father reference BUT whose chain of father references does not end in the root? I think this can only happen for cyclic references, for example A is the father of B, but B is the father of A, too. Such a "subtree" is not linked to the root and thus is not part of the main tree. I want to find such subtrees.

Of course, I am looking for a query that delivers those elements that lead to a cyclic reference no matter how long the chain of references may be.

Is that possible in SQL, or do I need an iterative solution?

Answer 1

SELECT  n.*, CONNECT_BY_ROOT(id), level
FROM    elements n
START WITH
        id IN
        (
        SELECT  MIN(id)
        FROM    (
                SELECT  id, CONNECT_BY_ROOT(id) AS root
                FROM    elements
                START WITH
                        id IN
                        (
                        SELECT  id
                        FROM    elements n
                        WHERE   CONNECT_BY_ISCYCLE = 1
                        CONNECT BY NOCYCLE
                                father_id = PRIOR id
                        )
                CONNECT BY NOCYCLE
                        id = PRIOR father_id
                )
        GROUP BY
                root
        )
CONNECT BY NOCYCLE
        id = PRIOR father_id

You may want to read this article:

• Finding loops in a tree