1

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 ?

IT NET
  • 63
  • 3
  • 10
  • 2
    Wow! What a great reason not to use a lousy data format. If you try to stuff a bunch of integer ids into a string, then you are going to suffer. The right way to store a list in SQL is to use a table, not a string. – Gordon Linoff Apr 22 '17 at 13:48
  • Would the right way be by creating a view? – IT NET Apr 22 '17 at 13:53
  • Gordon. Agreed. But I have no control of the received data. So I have to handle them as this. – IT NET Apr 22 '17 at 13:55

1 Answers1

1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I agree, that the way the many_eans is stored is not ideal. It is possible to do a query that extract and create the data as you describe? – IT NET Apr 22 '17 at 13:56
  • @ITNET Have a look [here](http://stackoverflow.com/questions/8056507/how-to-split-comma-separated-string-inside-stored-procedure) for some suggestions on how to explode a CSV string to separate records in another table in Firebird SQL. – Tim Biegeleisen Apr 22 '17 at 14:00