Oracle SQL- Add Column from second table if conditions for that row are met

Question

I would like to bring in column C from table 2 if columns A and B in table 1 the row of table 2 that match columns A and B.

Table 1

Column A         Column B
ABC123           XX470000

Table 2

Column A            Column  B              Column  C
ABC123,EFG123       XX470000,XX560000      Winner

Query:

Select * From
(
Select * From Table 1 
) T1.
(
Select * From Table 2 
) T2.

where T1.ColumnA in s2.ColumnA 
and T1.ColumnB in s2.ColumnB 
;

I have tried "like", "in" and "=" statements to match up the different tables. There are also thousands of rows where I would need this.

Answer 1

If you want to check for the presence each value in the csv list, then you can use the following join conditions:

select t1.*, t2.columnC
from table1 t1
inner join table2 t2 
    on  ',' || t2.columna || ',' like '%,' || t1.columna || ',%' 
    and ',' || t2.columnb || ',' like '%,' || t1.columnb || ',%' 

However, you should really fix your schema and store each value of the csv string on a separate table row (possibly using a bridge table). Storing delimited values in a table somehow defeats the purpose of a relational database. Recommended related reading: Is storing a delimited list in a database column really that bad?.

Answer 2

One option would be using REGEXP_SUBSTR() function within a SELECT .. FROM .. CONNECT BY level <= ... type query with '[^,]+' to split the ColumnA values of the Table2 into substrings seperated by commas, and then join with the other tables' ColumnA values in the matching condition in order to get the exact matches for those substrings :

SELECT t1.ColumnA, t2.ColumnC
  FROM
  (
    SELECT REGEXP_SUBSTR(t2.ColumnA,'[^,]+',1,level) AS ColumnA, ColumnC
      FROM Table2 t2
   CONNECT BY level <= REGEXP_COUNT(t2.ColumnA,',') + 1
      AND PRIOR sys_guid() IS NOT NULL
  ) t2
  JOIN Table1 t1
    ON t1.ColumnA = t2.ColumnA

Demo