CODEWITHSUNDEEP
sqlinner-join
IT NET
IT NET

Reputation: 63

SQL Adding a Column to table, based on a inner join

I have two firebird tables, T1 & T2.

T1 columns: pid, ean, desc

A T1 record: 1234, '102030', 'the desc'

and

T2 columns: eid, many_eans, eandesc 

A T2 record: 4321, '634232;**102030**;273728', 'the ean desc'

I can with this select query get my results:

select  T1.pid
      , T1.ean
      , T1.desc
      , T2.eandesc

  from table1 as T1
inner
  join table2 as T2 
    on T1.ean like '%' || T2.many_eans || '%'

But the query takes too long, therefore I want to add a Column to T1 something like compute by, that adds the T2.eandesc text to the T1 table where T1.ean like %T2.many_eans%

After adding the column I would just do this query to get my data.

select pid, ean, desc, eandesc from T1

How do I add the T2.eandesc to T1 ?

Upvotes: 1

Views: 3791

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Currently your T2 table is not normalized and you are storing CSV data in the many_eans column. I would advise you to restructure T2 such that one ean appears per record, e.g.

T2 columns: eid, ean, eandesc 

4321, '634232', 'the ean desc'
4321, '102030', 'the ean desc'
4321, '273728', 'the ean desc'

Then, the following simple join would suffice to get your result:

select
    T1.pid
    T1.ean
    T1.desc
    T2.eandesc
from table1 as T1
inner join table2 as T2 
    on T1.ean = T2.ean

Note that if a given ean could appear more than once in one of your current strings, then can use select distinct instead to ensure that you don't get duplicate results from T1 in your result set.

Upvotes: 1

Related Questions