Relationship web

1

Hello

I have a file like the below:

Parent, Child
A, B
B, C
A, E
B, A
C, D
G, E
E, F
etc

The above if put together in a linear format, it could be like:
A=B-C-D
|
G-E-F

Etc

  1. As you can imagine, a parent can have many children which can have their own different parents and children and so on.
  2. 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).
  3. Last, there could be a possibility of closed structures like the below or any other type/shape really:
    A-B
    \ /
    C

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.

Thanks!

2

A, B
B, A

That's not a logical relationship. I'm not sure how you'd resolve all those.

1 Like
3

Please provide real world example of this

4

Sounds like misuse of the terms parent and child. What is the real relationship being expressed?

5

Indeed this can cause issues so let's exclude it for now. Is there any way for the rest i.e. to identify all direct and indirect relationships?

6

There are a couple of different methods but they're based on the number of nodes and the end use of the "net".

So, how many nodes and what are you using this for?

7

The end use of the net is to have the whole relationship chains like A>B>C etc.
The nodes are up to 30 but it should be flexible enough to go through all the cycles to get any number of nodes? I have seen a PowerBI DAX solution that you need to know the nodes beforehand but I would like it to 'loop until' to get all possible nodes.