CODEWITHSUNDEEP
sqloracle-database
mr_coder
mr_coder

Reputation: 69

Combine two table in Oracle Sql

I have two tables. One is

ID   REFPARTS  
--   --------   
100   '1,2'   
101    '1'

Second table

PART_ID    AMOUNT 
------    --------  
1            50
2            25

Final Table

ID   REFPARTS  AMOUNT
--   --------  ------- 
100   '1,2'     75
101    '1'      50

How can I get final table?

Thanks in advance.

Upvotes: 0

Views: 64

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

This is a horrible data model. You should not be storing lists of numeric ids in a comma delimited string. SQL has a wonderful way to represent lists. It is called a "table", not a "string".

In your case, you could do:

select t1.id, sum(t2.amount)
from table1 t1 join
     table2 t2
     on replace(t1.refparts, ',', ''',''') like '%''' || t2.partid || '''%'
group by t1.id;

Even though you can do this using string manipulation, you should put your efforts into fixing the data model.

Upvotes: 1

RGruca
RGruca

Reputation: 204

WITH tab1 AS ( 
  SELECT 100 AS id_, '1,2' AS refparts FROM dual 
   UNION ALL
  SELECT 101 AS id_, '1'   AS refparts FROM dual 
),
tab2 AS (
  SELECT 1 AS part_id , 50 AS amount  FROM dual 
   UNION ALL
  SELECT 2 AS part_id , 25 AS amount  FROM dual 
)
SELECT t1.id_, t1.refparts, SUM(t2.amount)
  FROM (
        SELECT DISTINCT id_, trim(regexp_substr(refparts, '[^,]+', 1, LEVEL)) refparts
          FROM tab1
          CONNECT BY instr(refparts, ',', 1, LEVEL - 1) > 0
       ) t1Splited
 INNER JOIN tab1 t1 ON t1.id_     = t1Splited.id_
 INNER JOIN tab2 t2 ON t2.part_id = t1Splited.refparts
 GROUP BY t1.id_, t1.refparts
 ORDER BY t1.id_
  ;

Upvotes: 0

George Joseph
George Joseph

Reputation: 5932

I am splitting the comma seperated string to rows in my with clause "data" this is followed by a join with the second table and grouping by the id values.

create table t(id int, refparts varchar2(100))

insert into t values(100,'1,2');
insert into t values(101,'1');

create table t2(part_id int, amount int);

insert into t2 values(1,50);
insert into t2 values(2,25);

with data
  as (
select a.id
       ,rtrim(
        substr(a.refparts|| ','
               ,instr(','||a.refparts||',',',',1,lvl)
               ,instr(','||a.refparts||',',',',1,lvl+1) - instr(','||a.refparts||',',',',1,lvl)
                  )
            ,',') as col2
  from t a
  join (select level as lvl
          from dual
        connect by level<=10) b
     on b.lvl <=length(a.refparts) - length(replace(a.refparts,',','')) + 1        
     )
select a.id
       ,sum(b.amount) as summed_val
  from data a
  join t2 b
    on a.col2=b.part_id
group by a.id

Upvotes: 0

Related Questions