a3uge a3uge - 4 months ago 20
SQL Question

SQL hierarchical query: find full tree given an id of any parent-child (Oracle)

I'm struggling a hierarchical SQL query (using Oracle). Say I have table of widgets with unique ids. A widget can be copied only once, and we track the parent id from the new widget. For example, lets say widget 100 gets copied and becomes widget 101, then 103, then 105.

id parent_id
100
101 100
102
103 101
104
105 103

CREATE TABLE WIDGETS (ID NUMBER NOT NULL, PARENT_ID NUMBER);
INSERT INTO WIDGETS (ID, PARENT_ID) VALUES (100, null);
INSERT INTO WIDGETS (ID, PARENT_ID) VALUES (101, 100);
INSERT INTO WIDGETS (ID, PARENT_ID) VALUES (102, null);
INSERT INTO WIDGETS (ID, PARENT_ID) VALUES (103, 101);
INSERT INTO WIDGETS (ID, PARENT_ID) VALUES (104, null);
INSERT INTO WIDGETS (ID, PARENT_ID) VALUES (105, 103);


(102 and 104 is just dummy data)

I'm trying to make a query that displays the full history of a widget, using any of the ids in the relationship.

So if I give the query an id of '103' (or '100'... or '105'), I'd expect the query to return something like this:

id parent_id
100
101 100
103 101
105 103


I've tried using CONNECT BY PRIOR, but I would need the start by ID to use start with. For example, I can display the full tree giving the query the origin id:

select parent_id as from_id, id as to_id
from WIDGETS
start with id = 100
CONNECT BY PRIOR id = parent_id;

from_id to_id
null 100
100 101
101 103
103 105


But what if I don't necessarily know the start with id? Is it possible to find the origin ID and then get the full tree from there?

Answer

Calculate the start by traversing the tree backwards until you hit a leaf. SELECT parent_id AS from_id, ID AS to_id FROM WIDGETS START WITH ID = ( SELECT ID FROM WIDGETS WHERE CONNECT_BY_ISLEAF=1 START WITH ID = 105 CONNECT BY ID = PRIOR parent_id ) CONNECT BY PRIOR ID = parent_id;

Comments