sql-server add tag
artaxerxe
In MS Server, I try to get a hierarchy from a table modeled as child <-> parent relationship, with possible circular cases. My SQL would be kind of this:

```
with my_cte(childId, parentId, col1)
AS (
   SELECT r.childId, r.parentId, r.col1
      FROM My_Table r WHERE r.childId = 'x' AND (r.col1 = 1 or r.col1 = 2)
   UNION ALL 
   SELECT rel.childId, rel.parentId, rel.col1
	FROM My_Table rel INNER JOIN my_cte sd ON rel.childId = sd.parentId where (rel.col1 = 1 OR rel.col1 = 2) AND ...
   -- check that rel.childId <-> rel.parentId not in previous rows
)
SELECT DISTINCT * from shared_documents
```

I'm thinking the best thing would be to check somehow that the current row value for childId  and parentId wasn't present before. How can I check this? Is there another approach which you would recommend me?

Example table with records would be:

childId | parentId | col1 
-|-|-
1  | 3 | 1 
1 | 4 | 1 
3 | 5 | 1 
5 | 1 | 1  -- circular case
6 | 2 | 1 
5 | 7 | 1 

If I ask for hierarchy related to 1, I should get

childId | parentId | col1 
-|-|-
1 | 3 | 1 
1 | 4 | 1 
3 | 5 | 1 
5 | 1 | 1  -- circular case
5 | 7 | 1 

- 1 has two parents: 3 and 4.
- 3 has 1 parent: 5
- 5 has 2 parents: 1 and 7. But 1 brings the infinite loop, so the query should ignore this. And continue to the next row.
Top Answer
Jack Douglas
With a slightly simplified case:

<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=fde5996be19fd39ef6912cf6300fd1c9&hide=3

Notes:

* I've removed `col1` as I don't think it changes the principle for this query.
* Cycles are detected by keeping the initial ("root") `childId` in the recursion and exiting when it appears again as a parentId

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.