CODEWITHSUNDEEP
mysql
Ariful Islam
Ariful Islam

Reputation: 7675

How to match comma delimited value with other column in mySql

I have following two tables:

user:

+---------+--------------+
| user_id | skills       |
+---------+--------------+
|       1 | 1,2,3,5,4,14 |
|       2 | 1,2,3        |
|       3 | 3,4,5        |
|       4 | 1,2          |
+---------+--------------+

pskills:

+-----+--------+------+----------+
| PID | SKILLS | SPLI | status   |
+-----+--------+------+----------+
|   1 | 2,4    |    1 |          |
|   1 | 1      |    1 | required |
+-----+--------+------+----------+

I want to match values of SKILLS columns of table pskills. Such as if query is done with first row of pskills and join with user table then it will return User ID 1 because SKILLS 2,4 match with user id 1 only. How can i do this easily?

Upvotes: 1

Views: 234

Answers (1)

juergen d
juergen d

Reputation: 204746

Never store multiple values in one column!

You should normalize your tables like this

**user**
+---------+--------------+
| user_id | skills       |
+---------+--------------+
|       1 | 1            |
|       1 | 2            |
|       1 | 3            |
|       1 | ...          |
|       2 | 1            |
|       2 | 2            |
|         | ...          |
+---------+--------------+

**pskills**
+-----+--------+------+----------+
| PID | SKILLS | SPLI | status   |
+-----+--------+------+----------+
|   1 | 2      |    1 |          |
|   1 | 4      |    1 |          |
|   1 | 1      |    1 | required |
+-----+--------+------+----------+

Upvotes: 4

Related Questions