table1: t1_id, ids_in_table2
table2: t2_id, t2_data
In table1, the field 'ids_in_table2' represents a string of id numbers (integers only) separated by a space. For example "12 83 97 199". Those numbers are references to the field 't2_id' in table2. I want to obtain all the instances where 't2_id' appears in 'ids_in_table2' (my real example is more elaborate, but I simplified it to focus on what is important). My best query so far is:
SELECT table1.ids_in_table2, table2.t2_id FROM table1, table2 WHERE table1.ids_in_table2 REGEXP '\s?'+table2.t2_id+'\s?'
This is returning a partially correct list. For example, it wille fail when it returns values where ids_in_table2 = "15 16" and t2_id = "5". It will return this set of data because it found the 5 in the string from ids_in_table2.
I would appreciate any help especially concerning how I am using the REGEXP keyword in mysql and the "+" operator as string catenation. I cannot find sufficient documentation and examples of this keyword.