I want to detect potential cycles in a hierarchy. I have three tables, each have one parent, and one child column:
Table1 contains some nodes (in column child) and their parents (in column parent); Table2 contains all the parents of Table1 (in column child) and their parents (in column parent), and so on.
For example if A is a child of B, and B is a child of C and C is a child of A, then I have a cycle.
Is it possible to detect the cycles using sql commands?
The way you have structured your tables right now, the following SQL should work:
SELECT * FROM Table1 INNER JOIN Table2 on Table1.child = Table2.parent INNER JOIN Table3 on Table2.child = Table3.parent WHERE Table1.parent = Table3.child;