projects
| id | name | task |
|---|---|---|
| 1 | pro_1 | 2:3 |
| 2 | pro_2 | 1:2 |
| 3 | pro_3 | 2:3:4 |
tasks
| id | name |
|---|---|
| 1 | task_1 |
| 2 | task_2 |
| 3 | task_3 |
| 4 | task_4 |
I list one task however projects table that id number is 3 have 3 tasks (2:3:4) values. How can I do this?
SELECT id as proID, name,(SELECT name from tasks where id = substring_index(projects.tak,":",-1) ) as taskName
from projects where id = 3
Result List that I want
| proID | name | taskName |
|---|---|---|
| 3 | pro_3 | task_2 |
| 3 | pro_3 | task_3 |
| 3 | pro_3 | task_4 |
CodePudding user response：
SELECT *
FROM projects
JOIN tasks ON FIND_IN_SET(tasks.id, REPLACE(projects.task, ':', ','));
But I agree with Tim Biegeleisen's comment - normalize your structure, replace projects.task column with junction table.