GROUP_CONCAT repeating the same value

Question

I am currently using GROUP_CONCAT to look up names in table B based on comma separated ID's in table A. This is working fine except when the comma separated ID's are the same. Since I don't want the query to return just one value instead of the same value separated by a comma I can't use DISTINCT.

And when I'm not using DISTINCT the value gets repeated five times.

TABLE A
+--------------+--------+
| company      | order  | 
+--------------+--------+
| ABC Corp     | 1,1    |
| DEF Corp     | 1,2    |
+--------------+--------+

TABLE B
+----+--------------+
| id | fruit        | 
+----+--------------+
| 1  | Apple        | 
| 2  | Banana       | 
+----+--------------+

So with the query I would like to return 1,1 as 'Apple,Apple' instead of 'Apple'.

Any ideas on how to solve this?

Answer 1

SELECT tb.id,GROUP_CONCAT(tb.fruit) FROM ta
JOIN tb
ON FIND_IN_SET(tb.id,ta.`order`)>0
GROUP BY tb.id

Fiddle