CODEWITHSUNDEEP
sqloracle-database
blackJack
blackJack

Reputation: 15

Combining and checking table value on SQL (ORACLE)

Table 1

no name col1
1   a    a_1
2   b    b_1

Table 2

id   name parent
a_1   zz   c_1
b_1   yy   d_1
c_1   aa   null
d_1   bb   e_1
e_1   dd1  null

what i want to show is showing the all list name. for example table 1 name a has col1 name a_1 it will show the name on table 2, and then check the parent in the table 2 and show it and keep checking until it found null. the example is like below.. im sorry for my bad explanation

t1_name     t2_name  t2_name   t2_name
   a          zz        aa
   b          yy        bb        dd1

or shows like below

t1_name     t2_name
   a         aa/zz        
   b       dd1/bb/yy

what I've done is this query

select t1.name,t2.name as folder from table1 as t1 inner join table2 as t2 on t1.col1=t2.id

and I don't know how to check again in query... I am using oracle version 12.2.0.1.0 in SQL developer any help?

Upvotes: 0

Views: 69

Answers (3)

Popeye
Popeye

Reputation: 35900

You can use the hierarchical query as following:

SQL> -- Your data
SQL> with table1(no,name,col1) as
  2  (SELECT 1, 'a','a_1' FROM DUAL UNION ALL
  3  SELECT 2, 'b','b_1' FROM DUAL
  4  ),
  5  table2 (id, name, parent) as
  6  (select 'a_1', 'zz', 'c_1' from dual union all
  7  select 'b_1', 'yy', 'd_1' from dual union all
  8  select 'c_1', 'aa', null from dual union all
  9  select 'd_1', 'bb', 'e_1' from dual union all
 10  select 'e_1', 'dd1', null from dual)
 11  -- Your query starts from here
 12  SELECT
 13      T1.NAME    AS T1_NAME,
 14      T2.NAMES   AS T2_NAMES
 15  FROM TABLE1 T1
 16      JOIN (
 17          SELECT
 18              T2.ID,
 19              SYS_CONNECT_BY_PATH(T2.NAME, '/') AS NAMES,
 20              ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LEVEL DESC) AS L
 21          FROM TABLE2 T2
 22          CONNECT BY T2.PARENT = PRIOR T2.ID
 23      ) T2 ON T1.COL1 = T2.ID
 24  WHERE L = 1;

T1_NAME T2_NAMES
------- ---------------
a       /aa/zz
b       /dd1/bb/yy

SQL>

Cheers!!

Upvotes: 1

Viorel
Viorel

Reputation: 349

You want to get the rows from the first table and then recursively fetch all the rows from the second table until you reach a null parent, so you do:

with cte(NAME,
PARENT,
CURRENTPATH) as
 (select t1.NAME,
         t2.PARENT,
         t2.NAME   as CURRENTPATH
  from   TABLE1 t1
  join   TABLE2 t2 on t1.COL1 = t2.ID
  union all
  select t1.NAME,
         t2.PARENT,
         t1.CURRENTPATH || '/' || t2.NAME as CURRENTPATH
  from   cte t1
  join   TABLE2 t2 on t2.ID = t1.PARENT)
select NAME,
       CURRENTPATH
from   cte
where  PARENT is null;

Upvotes: 1

clxt1
clxt1

Reputation: 11

Which Oracle version are you using?

Upvotes: 0

Related Questions