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.
Advertisement
Answer
In MySQL this can be achieved as below
SELECT id, length FROM vehicles WHERE id IN ( 117, 148, 126) +---------------+ | id | length | +---------------+ | 117 | 25 | | 126 | 8 | | 148 | 10 | +---------------+ SELECT id,vehicle_ids FROM load_plan_configs WHERE load_plan_configs.id =42 +---------------------+ | id | vehicle_ids | +---------------------+ | 42 | 117, 148, 126 | +---------------------+
Now to get the length of comma separated vehicle_ids use below query
Output
SELECT length
FROM vehicles, load_plan_configs
WHERE load_plan_configs.id = 42 AND FIND_IN_SET(
vehicles.id, load_plan_configs.vehicle_ids
)
+---------+
| length |
+---------+
| 25 |
| 8 |
| 10 |
+---------+