I have a table profile look like this
table_1
| reg_id | reg_name | reg_colour |
|---|---|---|
| 1 | John | 2,3 |
| 2 | Doe | 1,3 |
| 3 | Jane |
table_2
| id_colour | colour | reg_num |
|---|---|---|
| 1 | black | 2,3 |
| 2 | white | 1,3 |
| 3 | green |
i want to join those column reg_colour in table 1 with id_color and the table should appear like this
| reg_id | reg_name | name_colour |
|---|---|---|
| 1 | John | white,green |
| 2 | Doe | black,green |
| 3 | Jane |
SELECT req_id,req_name,GROUP_CONCAT(colour SEPARATOR ‘, ‘) as ‘name_colour’
FROM table_1
JOIN table_2 ON table_2.id_colour = table_1.reg_colour
not working
>Solution :
You can try this query:
SELECT table_1.reg_id, table_1.reg_name, GROUP_CONCAT(table_2.colour SEPARATOR ',
') as 'name_colour'
FROM table_1
LEFT JOIN table_2 ON FIND_IN_SET(table_2.id_colour, table_1.reg_colour)
GROUP BY table_1.reg_id, table_1.reg_name;