I have the following schema (names changed), which I cannot change:
CREATE TABLE MyTable (
Id INT NOT NULL PRIMARY KEY,
ParentId INT NOT NULL
);
ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);
That is, each record is a child of another record.
The root node is the record with Id
equal to 0
.
I want to run query which will find all circular references. For example, with the data
INSERT INTO MyTable (Id, ParentId) VALUES
(0, 0),
(1, 0),
(2, 4),
(3, 2),
(4, 3),
(5, 6),
(6, 5),
(7, 5),
(8, 8);
the query should return
Id | Cycle
2 | 2 > 4 > 3 > 2
3 | 3 > 2 > 4 > 3
4 | 4 > 3 > 2 > 4
5 | 5 > 6 > 5
6 | 6 > 5 > 6
7 | 7 > 5 > 6 > 5
8 | 8
This is similar to my question How to write a query which finds all circular references when a table references itself? from last year, except for the following differences:
- The root node is hard-coded to have an
Id
of0
. - Any self-cycle (
8
in the example above) except for the root should be considered a cycle - The accepted answer runs into infinite recursion on the case
7 > 5 > 6 > 5
in the example above
I am having trouble coming up with a query to account for these. Suggestions?