Combining and checking table value on SQL (ORACLE)
no name col1 1 a a_1 2 b b_1
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 220.127.116.11.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>
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.
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 = t2.idto 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 listwhy 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 versionnow I want to show version too.. is it just add t1.version to the query?
- 18.104.22.168.0 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