Geordie Stew
Geordie Stew

Reputation: 31

Oracle CTE to find Grandparents and Children's Children

I'm an Oracle novice...comparatively speaking...

I'm trying to use CTE on an Oracle table which I've inherited. It's a little more complex than I'd have hoped, in that the "top level" isn't clearly defined.

create table testConnectBy ( parent number, child number );

insert into testConnectBy values ( 1,  1);
insert into testConnectBy values ( 1, 11);
insert into testConnectBy values ( 1, 12);
insert into testConnectBy values ( 2,  2);
insert into testConnectBy values ( 2, 13);
insert into testConnectBy values (11, 11);
insert into testConnectBy values (11, 20);
insert into testConnectBy values (11, 21);
insert into testConnectBy values (12, 12);
insert into testConnectBy values (12, 22);
insert into testConnectBy values (12, 23);
insert into testConnectBy values (12, 24);
insert into testConnectBy values (13, 13);
insert into testConnectBy values (13, 30);
insert into testConnectBy values (13, 31);
insert into testConnectBy values (30, 30);
insert into testConnectBy values (30, 40);

This query gets all of the required rows but has some duplicates in (which is first part of question, I guess, how is it best to remove). I've had to include nocycle as there are loop problems (parent == child).

select *
  from testConnectBy 
  start with parent = '1'
  connect by nocycle prior child = parent;

The next issue, is that I'd like to be able to enter 11 for example, and find out all of the parents and children. I don't know which parents or children are related to 11. Just that I need to find them by using 11.

This query only returns the children of 11. Is there any way to return both "directions"?

select *
  from testConnectBy 
  start with parent = '11'
  connect by nocycle prior child = parent;

Thanks in advance.

Upvotes: 1

Views: 285

Answers (1)

Srini V
Srini V

Reputation: 11355

For your first question, use the below. It doesn't have any duplicates which can be checked in the PATH

SELECT
            PARENT,
            CHILD,
            LEVEL,
            CONNECT_BY_ISLEAF AS ISLEAF,
            CONNECT_BY_ISCYCLE AS ISCYCLE,
            CONNECT_BY_ROOT PARENT
            || SYS_CONNECT_BY_PATH ( CHILD,
                                ' ~ ' )
                AS PATH
       FROM
            TESTCONNECTBY
       CONNECT BY
            NOCYCLE PARENT = PRIOR CHILD
       START WITH
            PARENT = '1';

This will return

1    1    1    0    1    1 ~ 1
1    11    2    0    1    1 ~ 1 ~ 11
11    20    3    1    0    1 ~ 1 ~ 11 ~ 20
11    21    3    1    0    1 ~ 1 ~ 11 ~ 21
1    12    2    0    1    1 ~ 1 ~ 12
12    22    3    1    0    1 ~ 1 ~ 12 ~ 22
12    23    3    1    0    1 ~ 1 ~ 12 ~ 23
12    24    3    1    0    1 ~ 1 ~ 12 ~ 24
1    11    1    0    1    1 ~ 11
11    20    2    1    0    1 ~ 11 ~ 20
11    21    2    1    0    1 ~ 11 ~ 21
1    12    1    0    1    1 ~ 12
12    22    2    1    0    1 ~ 12 ~ 22
12    23    2    1    0    1 ~ 12 ~ 23
12    24    2    1    0    1 ~ 12 ~ 24

If you want to avoid PARENT=CHILDREN, then

SELECT
            PARENT,
            CHILD,
            LEVEL,
            CONNECT_BY_ISLEAF AS ISLEAF,
            CONNECT_BY_ISCYCLE AS ISCYCLE,
            CONNECT_BY_ROOT PARENT
            || SYS_CONNECT_BY_PATH ( CHILD,
                                ' ~ ' )
                AS PATH
       FROM
            (SELECT * FROM TESTCONNECTBY WHERE PARENT <> CHILD)
       CONNECT BY
            NOCYCLE PARENT = PRIOR CHILD
       START WITH
            PARENT = '1';

The results are

1    11    1    0    0    1 ~ 11
11    20    2    1    0    1 ~ 11 ~ 20
11    21    2    1    0    1 ~ 11 ~ 21
1    12    1    0    0    1 ~ 12
12    22    2    1    0    1 ~ 12 ~ 22
12    23    2    1    0    1 ~ 12 ~ 23
12    24    2    1    0    1 ~ 12 ~ 24

For you second question, you can use the below snippet

SELECT
      *
FROM
      (SELECT
            PARENT,
            CHILD,
            LEVEL,
            CONNECT_BY_ISLEAF AS ISLEAF,
            CONNECT_BY_ISCYCLE AS ISCYCLE,
            CONNECT_BY_ROOT PARENT
            || SYS_CONNECT_BY_PATH ( CHILD,
                                ' ~ ' )
                AS PATH
       FROM
            TESTCONNECTBY
       CONNECT BY
            NOCYCLE PARENT = PRIOR CHILD
       START WITH
            PARENT = '1')
WHERE
      PARENT = 11
      OR CHILD = 11;

This query returns

1    11    2    0    1    1 ~ 1 ~ 11
11    20    3    1    0    1 ~ 1 ~ 11 ~ 20
11    21    3    1    0    1 ~ 1 ~ 11 ~ 21
1    11    1    0    1    1 ~ 11
11    20    2    1    0    1 ~ 11 ~ 20
11    21    2    1    0    1 ~ 11 ~ 21

since the parent 11 has a child 11. If you want to exclude self children, then

SELECT
      *
FROM
      (SELECT
            PARENT,
            CHILD,
            LEVEL,
            CONNECT_BY_ISLEAF AS ISLEAF,
            CONNECT_BY_ISCYCLE AS ISCYCLE,
            CONNECT_BY_ROOT PARENT
            || SYS_CONNECT_BY_PATH ( CHILD,
                                ' ~ ' )
                AS PATH
       FROM
            (SELECT * FROM TESTCONNECTBY WHERE PARENT <> CHILD)
       CONNECT BY
            NOCYCLE PARENT = PRIOR CHILD
       START WITH
            PARENT = '1')
WHERE
      PARENT = 11
      OR CHILD = 11;

Which will return

1    11    1    0    0    1 ~ 11
11    20    2    1    0    1 ~ 11 ~ 20
11    21    2    1    0    1 ~ 11 ~ 21  

Upvotes: 1

Related Questions