Get missing nodes in hierarchy column

Refresh

March 2019

Views

78 time

1

I have a table with hierarchy column which consists of numbers separated by colons, as well as number of current node and its parent:

id = '3:234:657:978' 
currNode = 978
parent = 657 

I also have a query which returns id's and some other columns from other tables, but some of the links are missing, for example 2 rows are returned, one with id of 3:234 and another one of id 3:234:567:890. I need the row with 3:234:567 id to form a hierarchy, but it`s not returned.

How can I join the table so I get the missing nodes (with fields other than id being NULL), but only the missing ones (excluding the ones which are not needed to form the hierarchy, e.g. are below the tree of the returned results)?

EDIT: Sample data:

CREATE TABLE ids (
id VARCHAR(100)
, currNode INT PRIMARY KEY
, parent INT
, name VARCHAR(50)
);

CREATE TABLE someotherdata (
data VARCHAR(10)
, currnode INT
);

INSERT ALL 
INTO ids(id, currnode, parent, name)
VALUES('3', 3, NULL, 'Node1')
INTO ids(id, currnode, parent, name)
VALUES('3:4', 4, 3, 'Node2')
INTO ids(id, currnode, parent, name)
VALUES('3:4:5', 5, 4, 'Node3')
INTO ids(id, currnode, parent, name)
VALUES('3:4:5:6', 6, 5, 'Node4')
INTO ids(id, currnode, parent, name)
VALUES('3:4:5:6:7', 7, 6, 'Node5')
SELECT * FROM dual; COMMIT;

INSERT ALL
INTO someotherdata (name, id)
VALUES('data1', '3:4')
INTO someotherdata (name, id)
VALUES('data2', '3:4:5:6')
SELECT * FROM dual; COMMIT;

Desired result (id is given as parameter to the query, here it equals to '3'):

id       name     data

3        Node1    NULL
3:4      Node2    data1
3:4:5    Node3    NULL
3:4:5:6  Node4    data2

(3:4:5:6:7 is excluded from the result since it is not needed to form hierarchy with records that return data)

1 answers

2

This is not so nice. but seems to work:

SELECT it.id, it.name, ost.data
FROM
(SELECT DISTINCT t.id, t.name
FROM ids t JOIN someotherdata st
 ON instr(':'||st.currnode||':', ':'||t.currnode||':') >0) it LEFT JOIN someotherdata ost
ON it.id = ost.currnode

Edit ok, this is nicer:

select distinct t.id, t.name, st.data
from ids t left outer join someotherdata st on t.id = st.currnode
start with t.id in (select ist.currnode from someotherdata ist)
connect by prior t.parent = t.currnode
order by t.id

Here is a sqlfiddle demo