mySQL Left Join on multiple values (REPLACE AND SUBSTRING_INDEX FUNCTION failed)

I am trying to join 2 tables tbl_questions and tbl_answers.

tbl_questions contains the qid and the answer columns tbl_answers stores the answerid and answerdesc columns.

tbl_questions data stores the multiple choice answers by encapsulating it with ["answer"] and separated by a comma like this ["16672","16673","16676"] any none multiple choice answers are stored like this 16688. I have to report on this data and cant do a join on any of the multiple choice questions, comes back blank understandably.

I have tried to run the following on the table

REPLACE(REPLACE(REPLACE(answer,'"',''),'[',''),']','') FROM tbl_questions

This cleans up the multiple choice answer to 16672,16673,16676 Then I separate the the answers using the comma with the following

SELECT Userid,SUBSTRING_INDEX(answer,',',1) as 'Option 1',
SUBSTRING_INDEX(SUBSTRING_INDEX(answer,',',2),',',-1) AS 'Option 2',
SUBSTRING_INDEX(SUBSTRING_INDEX(answer,',',-2),',',1) AS 'Option 3',
SUBSTRING_INDEX(answer,',',-1) AS 'Option 4'
FROM tbl_questions

This then creates new columns Option 1 - 4 but still doesn't solve my issue as the single answers appear in all new columns Option 1 -4. I don't feel the route i was taking to solve the problem is going to work. Does anyone know how i would join theses tables.

Thanks

Solution

You should start by trying to fix your schema and have a separate table to store each possible answer to each question, with one row per question/answer tuple. This would properly represent the relationship between your entites, and make the query much easier and efficient.

If th'at's not an option for you, then an alternative is find_in_set():

select ...
from tbl_questions q
inner join tbl_answers a 
    on find_in_set(
        a.answer_id,
        replace(replace(replace(q.answer,'"',''),'[',''),']','') 
    )

You could also consider using JSON functions, since your string format is understandable by MySQL as such (this requires MySQL 5.7 or higher):

select ...
from tbl_questions q
inner join tbl_answers a 
    on json_contains(replace(replace(q.answer,'"',''), a.answer_id)