0

A client has a not-so-small mysql table (~300K rows) that looks something like:

id int(11) AI PK 
answer_ids1 varchar(255) 
answer_ids2 varchar(255) 
answer_ids3 varchar(255) 
answer_ids4 varchar(255) 
answer_ids5 varchar(255) 
answer_ids6 varchar(255) 
answer_ids7 varchar(255) 
answer_ids8 varchar(255) 
answer_ids9 varchar(255) 
answer_ids10 varchar(255) 
answer_ids11 varchar(255) 
answer_ids12 varchar(255) 
answer_ids13 varchar(255) 
answer_ids14 varchar(255) 
answer_ids15 varchar(255) 
answer_ids16 varchar(255) 
answer_ids17 varchar(255) 
answer_ids18 varchar(255) 
answer_ids19 varchar(255) 
answer_ids20 varchar(255)

each answer_ids is a comma-separated string (so it is possible to have multiple answers in each answer column, for example: 3,7,10...)

In another table there are key->value pairs for the answer_id->answer_text.

My task is for each row to print a text like:

answer1={Quiz Answer 1}&answer2={Quiz Answer 2}&answer3={Quiz Answer 3}...

If there are more than one answer, then comma separate them.

I can do it with a for loop. I am just curious if there is a better way to do it using joins

justadev
  • 1,168
  • 1
  • 17
  • 32
  • "Efficient way" is the structure normalization. – Akina Aug 18 '21 at 18:41
  • Normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). – sticky bit Aug 18 '21 at 18:46
  • The table is given, the client will not change it for this task (this is legacy code, newer version already has better schema). So the 'efficient' way to handle this is using for loop, especially since there is no time contraint? – justadev Aug 18 '21 at 19:04
  • It would be helpful if you explicitly showed what the "other answer table" looks like. See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query). – Booboo Aug 18 '21 at 19:10
  • To do anything efficient with JOINS will require you to manipulate your data into a normalised, indexed form first. You've already ruled that out. Just thinking about handling variable length CSV data in SQL brings me out in spots, so don't go there. Head for your language of choice (PHP, Java, whatever) and do the job there. – Tangentially Perpendicular Aug 18 '21 at 19:55

0 Answers0