Order by comma separated values

Question

I have two tables body_part and video_customised.

Table -> body_part

+-----+-----------|
| id  | title     |  
------------------|
| 1     Abs
| 2     Chest
| 3     Neck
------------------

Table -> video_customised

+-----+-----------|
| id  | body_part |  
------------------|
| 1     2,1,3     |
------------------|

SQL

SELECT  vc.body_part  ,
  GROUP_CONCAT(bp.name ORDER BY vc.body_part) 
 as body_part_name FROM 
 `video_customised` `vc`
LEFT JOIN `body_part` as `bp` ON 
  `FIND_IN_SET`(bp.id, vc.body_part);

Result

+-----+-----------------------|
| body_part  | body_part_name |  
------------------------------|
| 2,1,3         Abs,Chest,Neck|     
------------------------------|

Expected Result:

+-----+-----------------------|
| body_part  | body_part_name |  
------------------------------|
| 2,1,3         Chest,Abs,Neck|     
------------------------------|

I want to GROUP_CONCAT body_part_name, its order should be same as body_part.

Database design mistake! Use a link table instead of comma seperated lists of keys — RiggsFolly, Jan 04 '19 at 13:04
You really need to normalize your data. This is neither scalable or maintainable. — Jay Blanchard, Jan 04 '19 at 13:04

score 4 · Accepted Answer · edited Jan 04 '19 at 13:12

You need to fix your data model. You should not be storing lists of numbers in a single field. Why?

In SQL tables, a column should contain a single value.
Numeric values should be stored as numbers not strings.
Foreign key relationships should be properly defined.
SQL has relatively poor string processing capabilities.
SQL is not designed to optimize queries on strings.

You should be using a junction/association table, with one row per body part and per video.

That said, sometimes we are stuck with other peoples really, really, really bad design decisions. In such a case, you can do what you want using find_in_set():

SELECT vc.body_part,
       GROUP_CONCAT(bp.name ORDER BY FIND_IN_SET(bp.id, vc.body_part)) as body_part_names
FROM video_customised vc LEFT JOIN
     body_part bp
     ON FIND_IN_SET(bp.id, vc.body_part); 
GROUP BY vc.body_part;

Order by comma separated values

1 Answers1