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)