An approach may be to first union the missing hierarchy from product and info into account and then generate the results... Since Product and info have relevant data to the hierarchy we need to combine them. I think this is likely the part that was the most troubling. Not all the hierarchy data was in one place. Once I realized that, the need for a union to allow the connect by prior to work was born.
So we join product to info to get the needed additional hierarchical data and union this to the account table. This results in a dataset containing all the needed parent child relationships.
We can then use connect by prior to traverse the hierarchy.
SELECT a.id_acc, level as lvl
FROM (SELECT id_acc, parent_acc FROM account
UNION
SELECT P.Account_ID, i.account_id FROM info i
INNER JOIN product p
on I.Product_ID=P.Product_ID) A
START WITH A.ID_ACC=76543
CONNECT BY prior a.id_acc=a.parent_acc
This would return:
Account Lvl
76543 1
18252 2
456452 3
34456 2
456567 2
Note: 456452 exists under 18252 so the relationship from the hierarchy is maintained; implying 76543 has 3 children (18252, 34456, 456567) and 18252 has a child 456452.
I think this is the best I can come up with given some assumptions about your question.
You could add some other bells and whistles to it if you need to know full path (sys_connect_by_path) or if you have loops in the data you need to eliminate (nocycle) or isLeaf (connect_by_isLeaf) to show childless records in the hierarchy.
SELECT a.id_acc
, level as lvl
, SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
, CONNECT_BY_ISLEAF "IsLeaf"
FROM (SELECT id_acc, parent_acc FROM account
UNION
SELECT P.Account_ID, i.account_id
FROM info i
INNER JOIN product p
on I.Product_ID=P.Product_ID) A
START WITH a.id_acc=76543
CONNECT BY nocycle prior a.id_acc=a.parent_acc
This would result in something like:
ID_acc lvl Path isLeaf
76543 1 /76543 0
18252 2 /76543/18252 0
456452 3 /76543/18252/456452 1
34456 2 /76543/34456 1
456567 2 /76543/456567 1
For a full list of features see Oracle Docs
I kind of downplayed the importance of not having loops in the data by using nocycle. Your results may be incorrect should loops in the data exist.
With a bit more work we can determine the top levels involved in to_Show though this:
select distinct ID_ACC
from (SELECT id_acc, parent_acc FROM account
UNION
SELECT P.Account_ID, i.account_id
FROM info i
inner join product p
on i.product_id=p.product_id) a
where parent_acc is null
connect by prior a.parent_acc = a.id_acc
start with id_acc in (select id from to_show)
and combine it with the above to achieve the desired results with no hardcoding.
SELECT a.id_acc
, level as lvl
, SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
, connect_by_isleaf "IsLeaf"
FROM (SELECT id_acc, parent_acc FROM account
UNION
SELECT P.Account_ID, i.account_id
FROM info i
INNER JOIN product p
on i.product_id=p.product_id) a
connect by nocycle prior a.id_acc=a.parent_acc
start with id_acc in
(
select distinct ID_ACC
from (SELECT id_acc, parent_acc FROM account
UNION
SELECT P.Account_ID, i.account_id
FROM info i
inner join product p
on i.product_id=p.product_id) a
where parent_acc is null
connect by prior a.parent_acc = a.id_acc
start with id_acc in (select id from to_show))
So all we did here was use start with in a subquery to get the top level domain of each record in to_Show, and invert the relationship so we crawled up the tree instead of down it and then used that distinct top level domain to return the entire hierarchy; thus showing the nodes in to_show; and the entire hierarchy in which it resides.
or as a CTE (Common Table Expression) for readability and refactoring to eliminate duplication.
with
--Let's combine the hierarchies so they are all in one place.
hsource as (
select id_acc, parent_acc from account
UNION
SELECT P.Account_ID, i.account_id
FROM info i
inner join product p
on i.product_id=p.product_id),
--Now lets get the top domain or root nodes of each item in To_Show
top_domain as (
select distinct id_acc
from (hSource) a
where parent_acc is null --this null compare is actually what tells us it's a root/Top domain! Assumption being all roots have null parent_Acc!
connect by prior a.parent_acc = a.id_acc
start with id_acc in (select id from to_show))
--Now lets use those top domains or root notes to render the hierarchy of each.
SELECT a.id_acc
, level as lvl
, SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
, connect_by_isleaf "IsLeaf"
FROM (Select id_acc, parent_acc from hSource) a
connect by nocycle prior a.id_acc=a.parent_acc
start with id_acc in (Select ID_ACC from Top_Domain)
Lastly... If my assumption that the parent_Acc is null is incorrect then you'll need to change the query in top_domain to the below: Connect by prior assumes that the top level will be null otherwise you get looping data which is why you can't simply set a.ID_ACC=a.parent_Acc, so we just alter two statements data using case statements to make a Parent_ACC is null to be a true statement. Technically the where clause could just be ID_ACC=parent_Acc, but I wanted to keep with the theme. The connect by prior line MUST be changed however. Remember up above when I said no loops in data... Well when ID_ACC=parent_ACC that causes a loop. I mean come on! how can I be my own parent? But we handled it using case statements.
with
--Let's combine the hierarchies so they are all in one place.
hsource as (
select id_acc, parent_acc from account
UNION
SELECT P.Account_ID, i.account_id
FROM info i
inner join product p
on i.product_id=p.product_id),
--Now lets get the top domain or root nodes of each item in To_Show
top_domain as (
select distinct a.id_acc
from (hsource) a
--Modification starts here...
where case when a.parent_acc =a.id_Acc then null else a.parent_Acc end is null
connect by prior (case when a.parent_acc=a.id_acc then null else a.parent_Acc end) = a.id_acc
--ends here
start with a.id_acc in (select id from to_show))
--Now lets use those top domains or root notes to render the hierarchy of each.
SELECT a.id_acc
, level as lvl
, SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
, connect_by_isleaf "IsLeaf"
FROM (Select id_acc, parent_acc from hSource) a
connect by nocycle prior a.id_acc=a.parent_acc
start with id_acc in (Select ID_ACC from Top_Domain)
