Bridge table where the same child belongs to one or more parents

Hello,

I am in the middle of building a bridge table for a ragged hierarchy that contains planning breakdown structure (PBS) data where the parent/child relationship is identified. However, the same child can belong to one or more parents and the users would also like to see the children below it of that same shared child across the parents. I need help on how to accomadate for a child that can belong to multiple parents. Has anyone dealt with this before? See example below.

PBS Hierarchy Table (Source Table)
Parent | Child
A | A1
A1 | A11
A11 | A111
B | B1
B1 | A11


BRIDGE Table (Current Design and Target Table)
Parent | Child | Level_from_Parent | Top_Flg | Bottom_Flg
A | A | 0 | Y | N
A | A1 | 1 | N | N
A | A11 | 2 | N | N
A | A111 | 3 | N | Y
A1 | A1 | 0 | N | N
A1 | A11 | 1 | N | N
A1 | A111 | 2 | N | Y
A11 | A11 | 0 | N | N
A11 | A111 | 1 | N | Y
A111 | A111 | 0 | N | Y
B | B | 0 | Y | N
B | B1 | 1 | N | N
B | A11 | 2 | N | N
B | A111 | 3 | N | Y
B1 | B1 | 0 | N | N
B1 | A11 | 1 | N | N
B1 | A111 | 2 | N | Y


Query Result of retreiving parents of child = 'A111' with current bridge table design
Parent | Child | Level_from_Parent
A | A111 | 3
A1 | A111 | 2
A11 | A111 | 1
A111 | A111 | 0
B | A111 | 3
B1 | A111 | 2

However I am expecting the following result (Root = top parent to avoid mixing incorrect parents with the child):
Root | Parent | Child | Level_from_Parent
A | A | A111 | 3
A | A1 | A111 | 2
A | A11 | A111 | 1
A | A111 | A111 | 0
B | B | A111 | 3
B | B1 | A111 | 2
B | A11 | A111 | 1
B | A111 | A111 | 0

Is your question how to construct the query to get the result you want?

The basic problem is the top flag is set incorrectly. All instances with A or B as the parent should have top flag set to true, not just the identity instance. Once you do that, the solution is simple.

It requires two passes. First identify all top nodes that have A111 as a child, then list the nodes under those top nodes:

select b.parent, b.child, s.child, s.level_from_parent - b.level_from_parent
from bridge b,
(select parent, child, level_from_parent from bridge where child = A111 and top_flag = 'Y') s
where b.parent = s.parent and b.level_from_parent <= s.level_from_parent;

Sorry for the confusion. My question is does the current proposed bridge table design support having the same child belonging to one or more parents. In addition, (as you mentioned) the sql used to correctly return the results where the child can have one or more parents using the bridge table. I will try the sql you provided to check if I get the expected results. Thank you. Furthermore, you mentioned that I set the top flag incorrectly where I should instead set the top flag = 'Y' for child records where its parent equals the top root parent (In this case the parent equals 'A' or 'B'). However, according to The Data Warehouse Toolkit (pg 165) the top parent identity record should only have its top flag = 'Y'. Do you mean I should create an additional flag field where I set its flag value to 'Y' for all child (and identity) records where its parent is equal to the top root parent?

It should look like this:

A | A | 0 | Y | N
A | A1 | 1 | Y | N
A | A11 | 2 | Y | N
A | A111 | 3 | Y | Y

The top flag should be set when the parent is a top node.

And bridge tables work fine for children with many parents. A Bill of Materials is a common example of such a hierarchy.

I ran the sql statement against the bridge table and got more than I was expecting. I did get the root parent on each record, along with the same child that I was interestd in (in this case A111). However, in addition all the children were returned for the top parent as well that may or not may be related to the shared child (A111). I see the sql that was provided that its doing just that where it returns all the children for the top parent(s) (highlighted in red below). However, I only want to see the children of the top parent that are directly related to the shared child (the parents above the shared child and the children below the shared child) or else it looks like the shared child has many more parents than it really posses. I hope this makes sense. Thanks again for the guidance.

SQL:
select b.parent, b.child, s.child, s.level_from_parent - b.level_from_parent
from bridge b,
(select parent, child, level_from_parent from bridge where child = A111 and top_flag = 'Y') s
where b.parent = s.parent and b.level_from_parent <= s.level_from_parent;

Expected Result:
(Root = top parent to avoid mixing incorrect parents with the child):
Root | Parent | Child | Level_from_Parent
A | A | A111 | 3
A | A1 | A111 | 2
A | A11 | A111 | 1
A | A111 | A111 | 0
B | B | A111 | 3
B | B1 | A111 | 2
B | A11 | A111 | 1
B | A111 | A111 | 0


SQL Query Result:
(Root = top parent to avoid mixing incorrect parents with the child):
Root | Parent | Child | Level_from_Parent
A | A | A111 | 3
A | A1 | A111 | 2
A | A11 | A111 | 1
A | A111 | A111 | 0
A | AX | A111 | 4 -- AX has no relation to A111 in the hierarchy
A | AY | A111 | 5 -- AY has no relation to A111 in the hierarchy
A | AZ | A111 | 3 -- AZ has no relation to A111 in the hierarchy
B | B | A111 | 3
B | B1 | A111 | 2
B | A11 | A111 | 1
B | A111 | A111 | 0