CODEWITHSUNDEEP
sqloracle-databasehierarchical-datarecursive-queryconnect-by
Nick
Nick

Reputation: 11

Oracle help using Connect By Root to return assembly top level parent

I'm trying to use CONNECT BY ROOT to do a recursive query that returns the top-level parent of an assembly part. My query so far is only able to return the parent one level above. I created a test case below that replicates the issue. You can also view it in fiddle.

In the real environment my output will have 10K+ rows so if this isn't an efficient way to do it let me know.

-- Create tables
CREATE TABLE t1 (
  TOOL VARCHAR2(50),
  PART VARCHAR2(50),
  PART_ID NUMBER
);

CREATE TABLE item_mstr (
  ITEM_ID NUMBER,
  ITEM VARCHAR2(50)
);

CREATE TABLE item_str (
  PARENT_ID NUMBER,
  CHILD_ID NUMBER
);

INSERT INTO t1(TOOL, PART, PART_ID) VALUES ('tool1', 'assy1', 201);
INSERT INTO t1(TOOL, PART, PART_ID) VALUES ('tool2', 'assy2', 202);
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (101, 'part1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (102, 'part2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (201, 'assy1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (202, 'assy2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (301, 'parent1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (302, 'parent2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (401, 'gparent1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (402, 'gparent2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (501, 'top_parent1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (502, 'top_parent2');
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (201, 101);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (202, 102);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (301, 201);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (302, 202);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (401, 301);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (402, 302);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (501, 401);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (502, 402);

-- Query
SELECT DISTINCT
t1.TOOL,
t1.PART,
CONNECT_BY_ROOT itm.ITEM TOP_PARENT
FROM item_str str
JOIN t1 on t1.PART_ID = str.CHILD_ID
JOIN item_mstr itm on itm.ITEM_ID = str.PARENT_ID
CONNECT BY PRIOR str.PARENT_ID = str.CHILD_ID
START WITH str.CHILD_ID = t1.PART_ID;

Here's the output I'm looking for:

TOOL   | PART   | TOP_PARENT
-----------------------------
tool1  | assy1  | top_parent1
tool2  | assy2  | top_parent2

Again the issue is the query returns the next parent, in this case 'parent1' and 'parent2', not the top-level.

One thing I noticed is when I add 'WHERE LEVEL > 1' after the joins the output returns nothing. I take that to mean the query sees nothing above the next level so it's not able to see the top-level parent. I'm not sure why though. Thanks for reading!

Upvotes: 0

Views: 64

Answers (1)

MT0
MT0

Reputation: 168588

You can descend the hierarchy using:

SELECT t.tool,
       t.part,
       m.item
FROM   t1 t
       INNER JOIN (
         SELECT CONNECT_BY_ROOT child_id AS child_id,
                parent_id AS leaf_id
         FROM   item_str
         WHERE  CONNECT_BY_ISLEAF = 1
         CONNECT BY child_id = PRIOR parent_id
       ) i
       ON (t.part_id = i.child_id)
       INNER JOIN item_mstr m
       ON (i.leaf_id = m.item_id);

or ascend the hierarchy using:

SELECT t.tool,
       t.part,
       m.item
FROM   t1 t
       INNER JOIN (
         SELECT child_id,
                CONNECT_BY_ROOT parent_id AS root_id
         FROM   item_str i
         START WITH NOT EXISTS ( SELECT 1
                                 FROM   item_str x
                                 WHERE  i.parent_id = x.child_id )
         CONNECT BY PRIOR child_id = parent_id
       ) i
       ON (t.part_id = i.child_id)
       INNER JOIN item_mstr m
       ON (i.root_id = m.item_id);

Which, for the sample data, both output:

TOOL PART ITEM
tool1 assy1 top_parent1
tool2 assy2 top_parent2

fiddle

Upvotes: 0

Related Questions