Re: SQL challenge to any gurus out there
Date: 21 Jul 2003 15:03:14 -0700
Message-ID: <336da121.0307211403.9a0c311_at_posting.google.com>
"GlenT" <glen.turner_at_bt.com> wrote in message news:<bf8cr9$5sv$1_at_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.
Use connect by query to autojoin table with itself. This is how I tested it:
1 select ta.t, ta.parent, tb.property 2 from af_glen_test ta, af_glen_test tb 3 where ta.parent in (select t
4 from af_glen_test tc 5 connect by prior t = parent 6 start with t = tb.t)
7 union
8 select t, null, property
9* from af_glen_test
SQL> / T PARENT PROPERTY
---------- ---------- ----------
A 1 A 2 B A 1 B A 2 B 3 B 4 C B 1 C B 2 C B 3 C B 4 C 5 D B 1 D B 2 D B 3 D B 4 D 6
Looks OK?
> Regards
> Glen
Received on Tue Jul 22 2003 - 00:03:14 CEST