Before I even start...I did not create this data model nor do I have any capability to change it. So, please no responses intended merely to explain how screwed up it is...I know.
Here's the issue.
A group may be comprised of either blurgs or other groups. So, I have three tables:
| MEMBERSHIP_MAP |
|---|
| PARENT_GROUP_ID |
| MEMBER_GROUP_ID |
| MEMBER_BLURG_ID |
So, whenever the MEMBER_GROUP_ID is populated, the MEMBER_BLURG_ID will be NULL and whenever the MEMBER_BLURG_ID is populated the MEMBER_GROUP_ID will be null.
The output we're looking for will show the groups that are directly assigned to the Blurg as well all groups that are indirectly assigned to the Blurg.
For example, Blurg Joe belongs to the PL/SQL Developers group. The PL/SQL Developers Group belongs to the Developers Group. The Developers Group belongs to the Nerd group. The Nerd group has no parent.
Blurg Joe also belongs to the Golfers group which has no parent.
So, what we'd want to see is something like this.
| BLURG_NAME | GROUP_NAME | Level |
|---|
| Joe | PL/SQL Developers Group | 1 |
| Joe | Developers | 2 |
| Joe | Nerd | 3 |
| Joe | Golfers | 1 |
How?
We can't seem to figure out what to use for the CONNECT BY clause because the first level are those Groups that are directly assigned to the Blurg and afterward, further up the hierarchy, Groups are connected to other Groups.
Thanks,
-Joe