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
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;