I need to do the exact opposite of the IN() sql statement.
I have two tables in my db:
Table1 has a string field which contains strings of ID Numbers of the records from Table2 seperated with commas, for example:
rs1("string") = "2,5,1,7"
Then, when I want to query all the mentioned records from Table2 I simply write:
strSQL = "SELECT * FROM tbl2 WHERE row_id IN(" & rs1("string") & ")"
Now I want to do the exact opposite: Have an ID Number from Table2 and query all the records from Table1 that have that ID Number in their string. I can't use the WHERE LIKE statement because there's a problem with the edge cases (lets say the row_id I'm looking for is 2):
- If I look for "WHERE string LIKE '%" & row_id & "%'", I will have problems with such strings: "6, 22, 42"
- If I look for "WHERE string LIKE '," & row_id & ",", I will have problems with such strings: "2, 5, 3" and "6, 7, 2"
- If I look for "WHERE string LIKE '," & row_id & "'" or "WHERE string LIKE '" & row_id & ",'", I will have problems with such strings: "5,23" and "26,8"
So I don't know how to query these things. Any help please?
Sorry if I explained it too complicatedly.