MySQL: Transform a CSV list of IDs to a CSV list of Names

Question

I have some groups. Each group has some elements, each element has an ID and a NAME.

GROUPS
|___________|_______________|
|GROUP_NAME |ELEMENT_IDS    |
|___________|_______________|
|ala        |15,16,18       |
|bala       |18,15          |
|coala      |16             |

ELEMENTS
|___________|_______________|
|ID         |NAME           |
|___________|_______________|
|15         |foo            |
|16         |bar            |
|18         |car            |

What kind of join (in MySQL) I should do in order to output the following:

|___________|_______________|_________________________________|
|GROUP_NAME |ELEMENT_IDS    |ELEMENT_NAMES                    |
|___________|_______________|_________________________________|
|ala        |15,16,18       |foo,bar,car                      |
|bala       |18,15          |car,foo                          |
|coala      |16             |bar                              |

Tip of today: never store data as comma separated items. It will only cause you lots of trouble. (One value per row is the SQL way.) — jarlh, Nov 06 '15 at 10:16

score 1 · Accepted Answer · edited Nov 06 '15 at 11:49

http://sqlfiddle.com/#!9/3aff79/7

SELECT g.group_name, g.element_ids,
GROUP_CONCAT(e.name) AS NAMES
FROM GROUPS as g 
INNER JOIN ELEMENTS as e ON
FIND_IN_SET(e.ID, g.ELEMENT_IDS)
GROUP BY GROUP_NAME;


| group_name | element_ids |       NAMES |
|------------|-------------|-------------|
|        ala |    15,16,18 | bar,foo,car |
|       bala |       18,15 |     car,foo |
|      coala |          16 |         bar |

Hope this helps.

MySQL: Transform a CSV list of IDs to a CSV list of Names

1 Answers1