Hierarchical query / table entry base

Question

Billing table:

      ID_ACC    PARENT_ACC
        76543    76543      <-edit
        18252    76543
        34456    76543
        456567   76543
        457564   65424
        45654242 6556756

Information table:

      ID     account_id  product_id
    875621     18252       98567
    875621     34456       98567

Product table:

    ACCOUNTID  PRODUCT_ID 
      456452     98567

The invoice table contains an account identifier. But some of the accounts ( ID_ACC ) have a parent account ( Parent_acc ). This example shows that

ID_ACC:   18252,
          34456,
          456567 

has the same parent_acc (76543).

Some children have information (information table)

(This example shows that 18252 and 34456 connect to the info table)

Further, some info.account_id contain a product (product table) (in this example, only 18252 accountid has a product with an account number: 456452. )

Account.id_acc=Info.account_id
Info.product_id=Product_id.product_id

:

76543,
18252,
34456,
456567,
456452.

( ) - , (). to_show , . 34456, , .

to_show , . "76543,18252,34456", , , ...

, , - , , ( ) to_show... / . , , , ( ..). , ?

create table account (
id_acc number,
parent_acc number
)
;
create table info
(
ID number,
Account_id number,
Product_id number
);
create table product (
Account_id number,
product_id number
);
create table to_show (
ID number
);
insert into account (id_acc,parent_acc)) values (76543,76543);
insert into account (id_acc,parent_acc) values (18252,76543);
insert into account (id_acc,parent_acc) values (34456,76543);
insert into account (id_acc,parent_acc) values (456567,76543);
insert into account (id_acc,parent_acc) values (457564,65424);
insert into account (id_acc,parent_acc) values (45654242,6556756);
insert into info values (875621,18252,98567);
insert into info values (875621,34456,null);
insert into product values (456452,98567);
insert into to_show values (34456);

Answer 1

, product info , ... product info , , , , , . . , , .

, product info, . , .

connect by prior .

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

:

Account Lvl
76543   1
18252   2
456452  3
34456   2
456567  2

: 456452 18252, ; 76543 3 (18252, 34456, 456567) 18252 456452.

, , , .

, (sys_connect_by_path), , (nocycle) isLeaf (connect_by_isLeaf), .

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

- :

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

. Oracle

, nocycle. , .

, to_Show, :

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)

.

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))

, , , , to_Show , , ; to_show; , .

CTE (Common Table Expression) .

with 
--Let 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 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)

... , parent_Acc null, , top_domain : connect by prior , , a.ID_ACC=a.parent_Acc, case, Parent_ACC is null . where ID_ACC=parent_Acc, . connect by prior . , ... , ID_ACC=parent_Acc . ! ? case.

with 
--Let 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)