19

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.

dreftymac
  • 31,404
  • 26
  • 119
  • 182
AndreKR
  • 32,613
  • 18
  • 106
  • 168
  • [this](https://stackoverflow.com/questions/46890617/restructuring-a-bad-database-with-php-loops-or-mysql/46892143#46892143) is how i should do this, the comment is here because i saw a edit on this post, then you can simply join after.. – Raymond Nijland Jun 08 '19 at 00:20

4 Answers4

16

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      |
+---------+

For more info visit http://amitbrothers.blogspot.in/2014/03/mysql-split-comma-separated-list-into.html

user13500
  • 3,817
  • 2
  • 26
  • 33
Amit
  • 194
  • 1
  • 6
5

I've answered two similar questions in as many days but not had any responses so I guess people are put off by the use of the cursor but as it should be a one off process I personally dont think that matters.

As you stated MySQL doesnt support table return types yet so you have little option other than to loop the table and parse the material csv string and generate the appropriate rows for part and material.

The following posts may prove of interest:

split keywords for post php mysql

MySQL procedure to load data from staging table to other tables. Need to split up multivalue field in the process

Rgds

Community
  • 1
  • 1
Jon Black
  • 16,223
  • 5
  • 43
  • 42
  • Not sure if you can do it without a loop, unless there is a preset maximum number of values that can be in the comma delimited values...you're sort of forced to a loop – Twelfth Oct 14 '10 at 18:49
  • Yes you'll have to loop - as stated. – Jon Black Oct 14 '10 at 19:01
  • Not the nicest solution because it takes the problem from the set theretical level to the procedural level and therefore can only be used as preparation, not in realtime (answers my "or", not my "either" question) but at least it works. Thanks. :) – AndreKR Oct 15 '10 at 07:36
  • 2
    that's causality for ya - crappy db design leads to crappy procedural fix :P – Jon Black Oct 15 '10 at 08:18
  • Heh, well put f00. @AndreKR - it's a csv field in a single column, the problem you've posed never was on the set theorectical level unfortunately. You got a procedural answer to a procedural question. I guess if there had been a limit to the number of values in that csv column (max of 10?) you could potentially do a set based solution. May want to normalize your database... – Twelfth Oct 15 '10 at 15:28
1

MySQL does not have temporary table reuse and functions do not return rows.

I can't find anything in Stack Overflow to convert string of csv integers into rows so I wrote my own in MySQL.

DELIMITER $$

DROP PROCEDURE IF EXISTS str_split $$
CREATE PROCEDURE str_split(IN str VARCHAR(4000),IN delim varchar(1))
begin
DECLARE delimIdx int default 0;
DECLARE charIdx int default 1;
DECLARE rest_str varchar(4000) default '';
DECLARE store_str varchar(4000) default '';

create TEMPORARY table IF NOT EXISTS ids as (select  parent_item_id from list_field where 1=0);
truncate table ids;
set @rest_str = str;
set  @delimIdx = LOCATE(delim,@rest_str);
set @charIdx = 1;
set @store_str = SUBSTRING(@rest_str,@charIdx,@delimIdx-1);
set @rest_str = SUBSTRING(@rest_str from @delimIdx+1);

if length(trim(@store_str)) = 0   then
    set @store_str = @rest_str;
end if;    


INSERT INTO ids
SELECT (@store_str + 0);

WHILE @delimIdx <> 0 DO
    set  @delimIdx = LOCATE(delim,@rest_str);
    set @charIdx = 1;
    set @store_str = SUBSTRING(@rest_str,@charIdx,@delimIdx-1);
    set @rest_str = SUBSTRING(@rest_str from @delimIdx+1);

select @store_str;
    if length(trim(@store_str)) = 0   then
        set @store_str = @rest_str;
    end if;    
    INSERT INTO ids(parent_item_id)
    SELECT (@store_str + 0);
END WHILE;

select parent_item_id from ids;
end$$
DELIMITER ;

call str_split('1,2,10,13,14',',')

You will also need to cast to different types if you are not using ints.

Sayed Abolfazl Fatemi
  • 3,678
  • 3
  • 36
  • 48
shrikeac
  • 161
  • 1
  • 5
0

You can also use REGEXP

SET @materialids=SELECT material FROM parttable where part_id=1;
SELECT * FROM material_id WHERE REGEXP CONCAT('^',@materialids,'$');

This will help if you want to get just one part. Not the whole table of course

Matthias
  • 1