All geek questions in one place

SQL SELECT to find circular references in tree-id-organized tree?

"Fun" with circular links:

Suppose I have an ELEMENTS table that contains a hierarchy of elements modeled by a father ID.

The father id field is null for the root.

All other entries have a nonzero father identifier with the primary key (autosequected) ( ID ) of the father element.

For example, using

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

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

But how can I find elements that have a valid link for the father, but whose chain of paternal links does not end with the root? I think that this can happen only for circular references, for example A is father B, but B is also father A. This "subtree" is not related to the root and, therefore, is not part of the main tree. I want to find such subtrees.

Of course, I'm looking for a query that delivers those elements that result in a circular link, no matter how long the link chain can be.

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

+6
source share
1 answer
 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 can read this article:

+5
source

Source: https://habr.com/ru/post/886788/

More articles:


All Articles