How to merge two tables in Oracle

Question

I have one EMPLOYEE table which is having employee Id and employee name mapping

another table is TEAM_EMP_MAP which is having team and employee mapping

so as per TEAM_EMP_MAP table a team with team id 1111 is having these employee EMP_1, EMP_2 and EMP_4. I need output like below with help of TEAM_EMP_MAP and EMPLOYEE table.

I have checked lot on google but not got success.

Storing comma separated values in a single column is a really bad idea. Do you have a chance to fix that broken database design? — , Jul 26 '20 at 17:07
I would echo previous comment, in that your design of TEAM_EMP_APP is seriously and fatally flawed. You need to study up on "data normalization". Those comma seperated values should each be their own row. Yes, that means you will be repeating ORG_ID values, but that, in itself, is not a concern. — EdStevens, Jul 26 '20 at 17:18
@a_horse_with_no_name - i am agree with you, i will fix this broken database designs. thanks for valuable reply. — Rajeev, Jul 26 '20 at 18:01
Good to hear :) Separating multi-valued fields into separate rows, with all the flexibility and validation that makes possible, is pretty much the reason for relational databases. — William Robertson, Jul 26 '20 at 18:16

forpas · Accepted Answer · 2020-07-26T16:12:44.680

0

One option is to use the operator LIKE in the ON clause of the join of the tables like this:

SELECT t.ORG_ID TEAM_ID, 
       e.EMP_NM EMPLOYEE_NAME 
FROM TEAM_EMP_MAP t INNER JOIN EMPLOYEE e
ON ',' || t.EMPLOYEES || ',' LIKE '%,' || e.EMP_ID || ',%'

See he demo.
Results:

> TEAM_ID | EMPLOYEE_NAME
> :------ | :------------
> 1111    | EMP_1        
> 1111    | EMP_3        
> 1111    | EMP_4

edited Jul 26 '20 at 16:12

answered Jul 26 '20 at 16:05

forpas

160,666
10
38
76

score 0 · Answer 2 · answered Jul 26 '20 at 16:12

0

Ok i understand, you need to fix your TEAM_EMP_MAP table with correct many to many associations.

I think you can use Regex (REGEXP_SUBSTR) i this case to split your comma separed columns. Have a look at this post to make it work.

answered Jul 26 '20 at 16:12

Orkad

630
5
15

score 0 · Answer 3 · answered Jul 26 '20 at 16:17

One approach would be to use REGEXP_SUBSTR and CONNECT LEVEL BY to split the content in the array of the second table.

Let me show you an example:

SQL> create table y ( id_emp number , name varchar2(2) ) ;

Table created.

SQL> insert into y values ( 101 , 'A' ) ;

1 row created.

SQL> insert into y values ( 104, 'B' ) ;

1 row created.

SQL> insert into y values ( 103 , 'C' ) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from t ;

        ID VAL
---------- -------------
      1111 101,104,103

SQL> select * from y ;

    ID_EMP NAME
---------- ----
       101 A
       104 B
       103 C

SQL> with emps as ( SELECT regexp_substr(val, '[^,]+', 1, LEVEL) as empid FROM t
  2  CONNECT BY regexp_substr(val, '[^,]+', 1, LEVEL) IS NOT NULL )
  3  select y.name , emps.empid from y inner join emps on ( y.id_emp = emps.empid ) ;

NAME EMPID
---- -----------------------------
A    101
B    104
C    103

score 0 · Answer 4 · answered Jul 26 '20 at 16:45

with t as (
select org_id
      ,employees txt
  from team_emp_map
 where org_id = 1111
)
,emp as ( -- split string into records
select org_id
      ,regexp_substr(t.txt, '\d+', 1, level) emp_id
  from t
connect by regexp_substr(t.txt, '\d+', 1, level) is not null
)
select emp.org_id
      ,employee.emp_nm
  from emp
      ,employee
 where emp.emp_id = employee.emp_id

How to merge two tables in Oracle

4 Answers4