mysql explode a field loop through the values and create a new column with the title associated with those values

Question

I have two tables users and services and i am try to write a single query that will create a new column skills. The values in the column skills will be the service_title which maps to the service_id stored in user_skills.

Below are the examples of a the tables used:

Table users:
+---------+---------------+----------------+----------------+
|   id    |   user_fname  |   user_lname   |   user_skills  |
+---------+---------------+----------------+----------------+ 
|   1     |    kiran      |    bhattarai   |     1,2        |  
|   2     |    sujan      |    bhattarai   |     2,4        |
|   3     |    manorath   |    dad         |     1,2,3,4    |
|   4     |    bhagawoti  |    mom         |     2,3        |
+---------+---------------+----------------+----------------+

Table services:
+-----------------+------------------+
|   service_id    |   service_title  |  
+-----------------+------------------+
|   1             |     cook         |
|   2             |     clean        |
|   3             |     grocessery   |
|   4             |     teach        |
+-----------------+------------------+

Currently i am using this query:

SELECT users.user_fname,
users.user_lname,
(SELECT GROUP_CONCAT(service_title)
FROM `services`
WHERE `service_id` IN (1,2,3,4)) as skills
FROM users
WHERE
id =3;

Result of the above query:

+---------------+----------------+----------------------------------------+
|   user_fname  |   user_lname   |                 skills                 |
+---------------+----------------+----------------------------------------+ 
|    manorath   |       dad      |     cook,clean,grocessery,teach        |
+---------------+----------------+----------------------------------------+  

Instead of using the IN (1,2,3,4) I tried IN (users.user_skills) because the values in user_skills changes all the time and the result was:

+---------------+----------------+----------------------------------------+
|   user_fname  |   user_lname   |                 skills                 |
+---------------+----------------+----------------------------------------+ 
|    manorath   |       dad      |                  cook                  |
+---------------+----------------+----------------------------------------+ 

Every time a new service is added i have to add that service_id in the IN (1,2,3,4,new service id) of my query which is not a proper solution. I have already tried using php and another query to do solve this, the disadvantage of doing that is it is slowing down the process. How should i solve this problem in a single query.

Answer 1

You can use FIND_IN_SET() to JOIN the two tables.

SELECT
  u.user_fname,
  u.user_lname,
  GROUP_CONCAT(s.service_title) as skills
FROM users u
LEFT JOIN services s
  ON FIND_IN_SET(s.service_id, u.user_skills)
WHERE u.id = 3

Note that a JOIN with a FIND_IN_SET() condition cannot utilize any index. And that can lead to poor performance.

In general it's a bad idea to store relations in a separated string column. See Is storing a delimited list in a database column really that bad?.

You should normalize your design and create a separate table for your many-to-many relation. The table would look like

Table users_services:
+---------+------------+
| user_id | service_id |
+---------+------------+
|   1     |    1       |
|   1     |    2       |
|   2     |    2       |
|   2     |    4       |
|   3     |    1       |
|   3     |    2       |
|   3     |    3       |
|   3     |    4       |
|   4     |    2       |
|   4     |    3       |
+---------+------------+

And the query would be

SELECT
  u.user_fname,
  u.user_lname,
  GROUP_CONCAT(s.service_title) as skills
FROM users u
LEFT JOIN users_services us ON us.user_id = u.id
LEFT JOIN services s ON s.service_id = us.service_id 
WHERE u.id = 3