Combining and checking table value on SQL (ORACLE)

Question

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?

As you cannot have a variable number of columns in the select list, you will have to either use the sys_connect_by_path function, or pivot/unpivot with xml — gsalem
– gsalem, Commented Dec 18, 2019 at 8:13
Did you try with a connect by prior t2.parent = t2.id to connect all the rows hierarchically? — Viorel
– Viorel, Commented Dec 18, 2019 at 8:24
@Viorel i tried but nothing happens.. and i don't know how to fix it... I'm new to this.. i tried something like this select name from table2 where parent=id — blackJack
– blackJack, Commented Dec 18, 2019 at 8:56

Viorel · Accepted Answer · 2019-12-18 09:37:49Z

1

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;

answered Dec 18, 2019 at 9:37

Viorel

3493 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

blackJack Over a year ago

number of WITH clause column names does not match number of elements in select list why i get this error? can you help me? thank you for ur response btw :)

Viorel Over a year ago

Interesting, you probably modified the query and either the aliases of the columns in both queries don't match or the list of aliases defined is the CTE does not match. For example CTE(col1, col3) as (select col1, col2 union all select col1, col2) or CTE(col1, col2, col3) as (select col1, col2 union all select col1, col2, col3) would give such an error. @stephen-090

Viorel Over a year ago

Here is a fiddle with your example. The query is at the end.

blackJack Over a year ago

what if I add 1 more column in table 1 to show, for example, the table 1 consists no, name,col1, and version now I want to show version too.. is it just add t1.version to the query?

Popeye · Accepted Answer · 2019-12-18 09:57:51Z

1

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

answered Dec 18, 2019 at 9:57

Popeye

36k4 gold badges12 silver badges31 bronze badges

2 Comments

blackJack Over a year ago

thank you for your response. from those queries if I have 1000 data, therefore I need to add 1000 data to the syntax query?

Popeye Over a year ago

No, You don't need to add data in the query. You will use direct tables. I have used CTE to generate sample data. You must use the query that is after -- Your query starts from here

clxt1 · Accepted Answer · 2019-12-18 08:10:05Z

0

Which Oracle version are you using?

answered Dec 18, 2019 at 8:10

clxt1

113 bronze badges

1 Comment

blackJack Over a year ago

12.2.0.1.0 version

Collectives™ on Stack Overflow

Combining and checking table value on SQL (ORACLE)

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related