separate records from field with point and comma separated values in Mysql

Asked

Viewed 106 times

1

I have two tables in mysql database, GROUPS table and PRODUCTS table.

GROUPS

+-----------------+
| grupoid         |
+-----------------+
| 1               |
| 2               |
| 3               |
+-----------------+

PRODUCTS

+-----------------+
|   id |  grupoid |
+-----------------+
|   1  |  1;2;3   |
+-----------------+

I need to select and separate each group by row as the example below.

Select result

+-----------------+
| produto |grupoid|
+-----------------+
|    1    |  1    |
|    1    |  2    |
|    1    |  3    |
+-----------------+

Thanks in advance for your attention.

mysql sql
  • 1

    search the function SUBSTRING_INDEX

1 answer

0


Solved.

I managed to find the solution here, follow link Comma-separated division of values in Mysql

So, my query went like this:

  SELECT gg.descricao,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(p.grupos, ';', g.grupoid), 
    ';', 
    -1
  ) as grupo
FROM produtos AS p
JOIN grupos AS g ON CHAR_LENGTH(p.grupos) - CHAR_LENGTH(REPLACE(p.grupos, ';', '')) >= g.grupoid - 1
INNER JOIN grupos_imeis as gi ON gi.grupoid = (SUBSTRING_INDEX(
    SUBSTRING_INDEX(p.grupos, ';', g.grupoid), 
    ';', 
    -1
  ))
INNER JOIN grupos AS gg ON gg.grupoid = SUBSTRING_INDEX(
    SUBSTRING_INDEX(p.grupos, ';', g.grupoid), 
    ';', 
    -1
  )

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.