Reputation: 31
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
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