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, as folder from table1 as t1 inner join table2 as t2 on

and I don't know how to check again in query... I am using oracle version 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,
 (select t1.NAME,
         t2.NAME   as CURRENTPATH
  from   TABLE1 t1
  join   TABLE2 t2 on t1.COL1 = t2.ID
  union all
  select t1.NAME,
         t1.CURRENTPATH || '/' || t2.NAME as CURRENTPATH
  from   cte t1
  join   TABLE2 t2 on t2.ID = t1.PARENT)
select NAME,
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
 13      T1.NAME    AS T1_NAME,
 14      T2.NAMES   AS T2_NAMES
 16      JOIN (
 17          SELECT
 18              T2.ID,
 19              SYS_CONNECT_BY_PATH(T2.NAME, '/') AS NAMES,
 21          FROM TABLE2 T2
 23      ) T2 ON T1.COL1 = T2.ID
 24  WHERE L = 1;

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



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

  • Which MySQL version are you using?
  • im sorry i forgot its oracle @jarlh
  • 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
  • Did you try with a connect by prior t2.parent = to connect all the rows hierarchically?
  • @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
  • 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 :)
  • 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
  • Here is a fiddle with your example. The query is at the end.
  • 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?
  • version
  • thank you for your response. from those queries if I have 1000 data, therefore I need to add 1000 data to the syntax query?
  • 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