The Oracle PL/SQL "Connect By" syntax is very
valueable when extracting parent to child relationship
in your data. Suppose, an organization table
exists where three entity types are defined:
corporate, division, and branch. Suppose, each type
has an unique id, and within the organization table.
Lets build a table call organization
id type name
1 corporate abc hq
2 division division a
3 division division b
4 branch branch 1
5 branch branch 2
6 branch branch 3
7 branch branch 4
Lets build a table called organization_association that
defines the parent to child relationships
Parent_Id 1,1,2,2,3,3
Child_Id 2,3,4,5,6,7
If I start at the headquarters where
id=1
select id,type,name from organization
where
id in
(
select child_id from organization_association
where
start with parent_id in (1)
connect by prior child_id=parent_id
)
Produces
Organizations with ids equal to (2,3,4,5,6,7)
If I start with division id=2
select id,type,name from organization
where
id in
(
select child_id from organization_association
where
start with parent_id in (2)
connect by prior child_id=parent_id
)
Produces
Organizations with ids equal to (4,5)
|
No responses found. Be the first to respond and make money from revenue sharing program.
|