I have an unnormalized table with a column containing a comma separated list that is a foreign key to another table:
+----------+-------------+ +--------------+-------+
| part_id | material | | material_id | name |
+----------+-------------+ +--------------+-------+
| 339 | 1.2mm;1.6mm | | 1 | 1.2mm |
| 970 | 1.6mm | | 2 | 1.6mm |
+----------+-------------+ +--------------+-------+
I want to read this data into a search engine that offers no procedural language.
So is there a way to either make a join on this column or run a query on this data that inserts appropriate entries into a new table? The resulting data should look like this:
+---------+-------------+
| part_id | material_id |
+---------+-------------+
| 339 | 1 |
| 339 | 2 |
| 970 | 2 |
+---------+-------------+
I could think of a solution if the DBMS supported functions returning a table but MySQL apparently doesn't.