geek2000 geek2000 - 2 months ago 11
SQL Question

Detect cycles using sql

I want to detect potential cycles in a hierarchy. I have three tables, each have one parent, and one child column:

Table1's parents are Table2's children and Table2's parents are Table3's children

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?

Answer

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;
Comments