I have a file like the below:
The above if put together in a linear format, it could be like:
- As you can imagine, a parent can have many children which can have their own different parents and children and so on.
- Also, a relationship can have both directions (i.e. A=B where A is a parent of B but also B is a parent of A).
- Last, there could be a possibility of closed structures like the below or any other type/shape really:
Is there a way to check a particular A, B etc what has as direct or indirect relationships?
I.e. obviously for A, the first step would be to filter for Parent=A and Child=A and get the other respective columns. But then, for each of the other values, we will need to do the same and so on until we exhaust the network where A is part of.
I tried to find something and came across the recursive CTEs but not sure if that will capture everything or is there any other tool like Python, R, etc?
Another approach is to somehow create tables and generate the Cartesian products for each table but I am not sure if that is something proven.