SQL challenge to any gurus out there

From: GlenT <glen.turner_at_bt.com>
Date: Fri, 18 Jul 2003 09:49:39 +0100
Message-ID: <bf8cpn$5sh$1@pheidippides.axion.bt.co.uk>

Hi,
I am trying to create a view which shows inheritence but cannot get this right. I have provided the table and data to assist anyone kind enough to help us plus details of what we are trying to achieve. 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; We can see that this is a simple child-parent relationship.

If we do a select * we would get the following result: TYPE PARENT PROPERTY
------- ------ ----------
A null 1
A null 2
B A 3
B A 4
C B 5
D B 6

What we are trying to achieve would look like this: TYPE PARENT 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

What we are trying to achieve is for every type to show all it's inheritance i.e. type B inherits from type A because A is it's parent so the properties for type B are 3 and 4 plus the properties for type A which are 1 and 2. Type C would have it's own properties (5) plus those from type B (3,4) which is it's parent plus those of type A (1,2) which is B's parent.

I have tried using the connect by prior but with no success as the inheritence could be n layers deep. If anyone out there likes a challenge and can help - we would be extremely grateful. Regards
Glen Received on Fri Jul 18 2003 - 03:49:39 CDT