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?


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;

Tutorial: Joining Tables: Databases for Developers, It only returns rows which match the join conditions in both tables. The simplest join checks if the values in a column from one table equal the� The merge statement compares each row in the members table with each row in the member_staging table based on the values in the member_id columns (see the ON clause above).. If the values in member_id columns of both tables are equal, the MERGE statement updates the first name, last name, and rank from the members table to the member_stagingtable only if the values of first name, last name, or


Which Oracle version are you using?

Diving Into Oracle MERGE Statement, Database SQL Reference You can specify conditions to determine whether to update or insert into the target table or view. You must have the INSERT and UPDATE object privileges on the target table and the SELECT That is, the DELETE WHERE condition evaluates the updated value, not the original value that was� In this article we are going to learn how to combine two tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN in SQL with examples which support both SQL Server and MySQL. Refer below tables for the following examples. Table: Employees. Sample data for employees. Table: Departments. Sample data for departments. Use SQL Server or MySQL to


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

Combining and checking table value on SQL (ORACLE), First, specify the target table ( target_table ) which you want to update or insert into in the INTO clause. Second, specify the source of data ( source_table ) to be updated or inserted in the USING clause. Third, specify the search condition upon which the merge operation either updates or inserts in the ON clause. Check whether each table has the same number of rows for each set of values. You can do this by union alling the two tables together with a couple of extra columns. One to count the rows from the first table, the other for the second. By returning the values 1 or 0 you can sum these up to get the count.


MERGE Statement, 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: Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.


Introduction to the Merge Statement, The MERGE statement reduces table scans and can perform the operation in NOT MATCHED THEN INSERT (id, address) VALUES (h.emp_id, h.address); IF SQL%ROWCOUNT = 0 THEN INSERT INTO dest_tab (object_id, owner, For more detailed information on the cookies we use, please check our Privacy Policy. Description. The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement.. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.


Oracle / PLSQL: Combining the AND and OR Conditions, Use the SQL MERGE statement to synchronize changes made in one table with The MERGE statement is used to make changes in one table based on values If you're interested in why, check out this article. Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. We have requirement that we need to compare data from B with first two columns of table A and update/insert in the table B with values in 3rd and 4th column of table A. by update/insert i mean we should either update the two columns where match is found and replace them with entries from table A(3rd and 4th column) or else Insert an additional