mysqlselectgroup-concat

how to get description on column have multiple id in array to get the detail id mysql


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;