create list from a comma separated key in mysql

Question

I have data set like this in MySQL:

table order
order_id   item_id        
1          A,B,C
2          B,D,E

and

table item 
item_id    item_name
A          Candy
B          Beer
C          Cookies
D          Jam
E          Cigarette

How can I return data like this:

order_id   item_id    item_name
1          A,B,C      Candy,Beer,Cookies
2          B,D,E      Beer,Cookies,Cigarette

this sounds like a healthy diet, whaddya think @vhu. Just once begging for non KEYWORD or RESERVED WORD questions to appear — Drew, Sep 14 '15 at 05:11
The `order` table is poorly designed. You should consider having a row per item in the order, perhaps in an `order_items` table. — Nate Barbettini, Sep 14 '15 at 05:25
greetings @kekeskuya, I wrote up an answer [here](http://stackoverflow.com/a/32620163) recently on storing data in a junction table, and normalization, and sample statements to get to your data. Check it out some day and good luck ! — Drew, Sep 17 '15 at 19:19

score 1 · Answer 1 · answered Sep 14 '15 at 05:48

1

You can try below query-

SELECT o.order_id, o.item_id, GROUP_CONCAT(itm.item_name) 
FROM `order` AS o JOIN item AS itm ON FIND_IN_SET(itm.item_id,o.item_id) 
GROUP BY o.order_id;

answered Sep 14 '15 at 05:48

Zafar Malik

6,734
2
19
30

but it is not optimized way you should use a junction table for both tables relation for better performance. – Zafar Malik Sep 14 '15 at 05:50

score -1 · Answer 2 · answered Sep 14 '15 at 06:31

-1

Try This...

select o.order_id, o.item_id, GROUP_CONCAT(i.item_name SEPERATOR separator ',')
FROM order o
INNER JOIN item i ON on (find_in_set(i.item_id,o.item_id))
GROUP BY o.order_id

answered Sep 14 '15 at 06:31

Rohit Gaikwad

817
2
8
24

create list from a comma separated key in mysql

2 Answers2