Q
Problem solve Get help with specific problems with your technologies, process and projects.

Showing inheritance

We are trying to show inheritance. We have a table with a parent-child relationship:

SELECT  *
FROM    glen_test;

TYPE    PARENT PROPERTY
------- ------ --------
A       null          1
A       null          2
B       A             3
B       A             4
C       B             5
D       B             6
The table is interpreted like this:
  • Type A has properties 1 and 2.
  • Type B has properties 1, 2, 3 and 4: it inherits properties 1 and 2 from its parent (A), and has properties 3 and 4 directly.
  • Type C has all the properties of all its ancestors (1 and 2 from A, 3 and 4 from B) as well as its own property (5).
  • Type D is like C
We want to show all the properties that each type has and the ancestor (if any) from which they were inherited, like this

TYPE   ANCESTOR PROPERTY
------ -------- --------
A      null            1
A      null            2
B      A               1
B      A               2
B      null            3
B      null            4
C      A               1
C      A               2
C      B               3
C      B               4
C      null            5
D      A               1
D      A               2
D      B               3
D      B               4
D      null            6
We've tried using CONNECT BY ... PRIOR but with no success. Here is the code used to create the table:
CREATE TABLE  glen_test
( 
    type      VARCHAR2 (10), 
    parent    VARCHAR2 (10), 
    PROPERTY  NUMBER
);

INSERT INTO glen_test ( type, parent, property )
    VALUES ( 'A', NULL, 1); 
INSERT INTO glen_test ( type, parent, property )
    VALUES ( 'A', NULL, 2); 
INSERT INTO glen_test ( type, parent, property )
    VALUES ( 'B', 'A', 3); 
INSERT INTO glen_test ( type, parent, property )
    VALUES ( 'B', 'A', 4); 
INSERT INTO glen_test ( type, parent, property )
    VALUES ( 'C', 'B', 5); 
INSERT INTO glen_test ( type, parent, property )
    VALUES ( 'D', 'B', 6); 
COMMIT;


Thanks for including the code: it makes my job a lot easier.

My solution involves three main steps:

  1. Normalizing the Data
  2. Bottom-Up Hierarchical Query
  3. Finding the Root in Hierarchical Queries

1. Normalizing the Data The table glen_test is not normalized in that a single fact can be stored repeatedly. (E.g., the fact that A is the parent of B appears on two rows, namely the ones with properties 3 and 4). It would be clearer to have one table that just stored the parent-child relationships, and a second table that just stored the direct properties. I'll pretend you've done that by using views.

CREATE OR REPLACE VIEW  parent_child
AS
SELECT DISTINCT
        type,
        parent
FROM    glen_test;

SELECT  *
FROM    parent_child
ORDER BY
        type;

TYPE       PARENT
---------- ----------
A          null
B          A
C          B
D          B


CREATE OR REPLACE VIEW  property
AS
SELECT  type,
        property
FROM    glen_test;

SELECT  *
FROM    property
ORDER BY
        type,
        property;

TYPE         PROPERTY
---------- ----------
A                   1
A                   2
B                   3
B                   4
C                   5
D                   6

Continue to Part 2: Bottom-up hierarchical query...


Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close