Need to match a field with another that has commas on it’ s value

I would like to match the field values of “FORMULATION” from TABLE 1 to “C_TEST_ARTICLE” from table 2, that has mutiple of these formulation sepparated by commas.

Table 1:

+----------------+--------------------+
| SAMPLE_NUMBER  |    FORMULATION     | 
+----------------+-----------+--------+
|     84778      | S/200582/01-TA-002 |      
|     84777      | S/200582/01-TA-002 |
|     81691      | S/200451/01-TA-011 |
|     81690      | S/200451/01-TA-011 |
+----------------+-----------+--------+

TABLE 2

+-----------------------+--------------------------------------+------------------+
|                     C_TEST_ARTICLE                           | C_REPORT_NUMBER  | 
+----------------+-----------+---------------------------------+------------------+
|      S/200180/03-TA-001,S/200180/03-TA-002                   |       16698      |      
| S/200375/01-TA-001,S/200375/01-TA-002,S/200375/01-TA-003     |       15031      |
+--------------------------------------------------------------+------------------+

What I want form all of this, is that the each of these “C_TEST_ARTICLES” has a “C_REPORT_NUMBER”, so I would like to get all those “SAMPLE_NUMBERS” from table 1, so in that way, I would have the samples related to the report number.

Answer

you could try using LIKE

select SAMPLE_NUMBER  
from table1 
INNER JOIN table2 ON c_test_article like concat('%', formulation , '%'')

Advertisement

Answer