I have relationship data in the form:
Parent ID ParentName ParentType RelatedToID RelatedToName RelatedType
----------------------------------------------------------------------------
1 A Business 2 B Individual
1 A Business 4 D Business
1 A Business 3 C Business
1 A Business 6 F Business
1 A Business 3 C Business
1 A Business 9 I Business
1 A Business 9 I Business
1 A Business 3 C Business
1 A Business 12 L Business
1 A Business 5 E Business
2 B Individual 1 A Business
2 B Individual 3 C Business
2 B Individual 3 C Business
2 B Individual 6 F Business
2 B Individual 3 C Business
2 B Individual 4 D Business
2 B Individual 4 D Business
3 C Business 1 A Business
3 C Business 1 A Business
3 C Business 2 B Individual
3 C Business 10 J Business
3 C Business 6 F Business
3 C Business 14 N Business
3 C Business 4 D Business
3 C Business 7 G Business
3 C Business 1 A Business
3 C Business 2 B Individual
4 D Business 2 B Individual
4 D Business 3 C Business
4 D Business 3 C Business
4 D Business 10 J Business
4 D Business 1 A Business
4 D Business 1 A Business
4 D Business 7 G Business
5 E Business 1 A Business
5 E Business 1 A Business
6 F Business 2 B Individual
6 F Business 1 A Business
6 F Business 3 C Business
6 F Business 3 C Business
6 F Business 1 A Business
7 G Business 3 C Business
7 G Business 4 D Business
7 G Business 3 C Business
7 G Business 3 C Business
8 H Individual 9 I Business
8 H Individual 9 I Business
9 I Business 1 A Business
9 I Business 8 H Individual
10 J Business 3 C Business
10 J Business 3 C Business
10 J Business 13 M Business
10 J Business 3 C Business
10 J Business 4 D Business
10 J Business 11 K Individual
11 K Individual 10 J Business
11 K Individual 13 M Business
11 K Individual 10 J Business
11 K Individual 13 M Business
12 L Business 1 A Business
13 M Business 11 K Individual
13 M Business 10 J Business
I'm using DiagrammeR to make a relationship chart based on this data. I need to transform this data in SQL to feed into graphviz. i.e:
- A column in my final table in the form of ParentID "->" RelatedToID (e.g "A->B")
- exclude any redundant backwards relationships i.e A->B & B->A should be reduced to A->B
I'm willing to go 5 levels into the relationship tree in my data prep step. Ultimately this example above should reduce to this:
ReadySet
A->B
A->D
A->C
A->F
A->I
A->L
A->E
B->C
B->F
B->D
C->J
C->F
C->N
C->D
C->G
D->J
D->G
H->I
J->M
J->K
K->M
which in GraphViz results in:
What I have tried and my difficulty:
I started with defining the parents in which the ParentType = 'Individual' I then used self joining to obtain the hagiarchy on a row level.
What I want (and can't seem to do) is, produce a single SQL table that will produce ReadySet if a user makes a selection Name that is contained within the breadth of the relationship tree i.e if A is selected then ReadySet or if M is selected then ReadySet… There are obviously more parent ID's/names in my entire dataset.
You need a table to define the edges between the nodes and their direction.
"nodes" here is whatever table actually holds the data.
If the relationship is two way, we need to rows, one for A -> B and one for B -> A.
Then do a recursive CTE to find all the matching rows.
The first part of the union is the starting condition, and the second is the recursion which joins with the CTE.
For example, if we set up our edges like so:
Everything has a path from each other, except 6 which is one way to 5. If we ask for 5, we'll get all the edges except 6, 5. If we ask for 4, we'll get nothing because 4 has no outgoing connections.
And if we
select distinct to_id from ready_set;
starting at 5 we'll get just the node IDs 1, 2, 3, 4, and 5. No 6.Try it.