Search code examples
sqlpostgresqljoinconcatenation

SQL join on attribute value contained in another field

I would like to perform a join on attr_1 and attr_2 when:

  • attr_1 = attr_2
  • attr_1 is contained in attr_2

A good match is performed if attr_1 is one of the value of attr_2 separated by '/' .

attr_1 attr_2
SOR:562 ACCU:5698A/SOR:22/SOR:562
SOR:120 SOR:120/SOR:125
SOR:89 SOR:1001/ACCU:569/SOR:56239/SOR:89

But I don't want those matches as SOR:89 and SOR:899912 because it's not the same value (89 != 899912)

attr_1 attr_2
SOR:89 SOR:899912

How could I avoid wrong matches? (I tried this but it doesnt work:

on t.attr_1 LIKE CONCAT('%',s.attr_2,'%'))

Solution

  • Convert the second attribute to an array:

    select *
from first_table t1
  join second_table t2 on t1.attr1 = any(string_to_array(t2.attr2, '/'))