Aggregate columns but distinct terms should be inserted

Question

I have two table and I want to merge them

TERMS_TABLE

 ID  | TERMS
 309 | 'hardware'
 309 | 'software'
 309 | 'computer'



TFIDF_TABLE

  ID | TERMS
 309 |'computer,phone,mp3....'

Now I want to add TERMS column of TERMS_TABLE to terms column of TFIDF_TABLE but If TFIDF_TABLE already contains TERMS of TERMS_TABLE then I should not insert this term to the NEW_TFIDF_TABLE , like that

result should be:

NEW_TFIDF_TABLE

  ID | TERMS
 309 |'computer,phone,mp3....,hardware,software'

How can I do that ?

Answer 1

If you use Oracle 11 you can try this:

select t3.id, t3.terms||','||t4.terms terms from 
(
select t1.id, listagg(t1.terms,',') within group (order by t1.terms)  terms
from terms_table t1 join tfidf_table t2 on  t1.id=t2.id
where instr(t2.terms,t1.terms)=0 
group by t1.id ) 
t3 right outer join tfidf_table  t4 on t3.id=t4.id

On Oracle 10 you could try

select t3.id, t3.terms||','||t4.terms terms from 
(
select t1.id, wm_concat(t1.terms)  terms
from terms_table t1 join tfidf_table t2 on  t1.id=t2.id
where instr(t2.terms,t1.terms)=0 
group by t1.id ) 
t3 right outer join tfidf_table  t4 on t3.id=t4.id