MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE products ( 
Product_ID INT ,
Product_Code VARCHAR(20) ,
Assign_Key VARCHAR(20) );

INSERT INTO  products  VALUES
("1","ABC","23,45"),
("1","HIJ","23"),
("1","KLM","45"),
("2","DEF","10,28"),
("2","GHI","10"),
("2","NOP","28");

Records: 6  Duplicates: 0  Warnings: 0


CREATE TABLE assign ( 
Product_ID INT ,
Assign_ID INT ,
Value_ID INT );

INSERT INTO  assign  VALUES
("1","23","1"),
("1","45","2"),
("2","10","3"),
("2","28","4");

Records: 4  Duplicates: 0  Warnings: 0


-- I named the table `value`, because `values` is a reserved word.
CREATE TABLE value ( 
Value_ID INT ,
Value_name VARCHAR(20) );

INSERT INTO  value  VALUES
("1","Red"),
("2","Large"),
("3","Blue"),
("4","Small");

Records: 4  Duplicates: 0  Warnings: 0


SELECT @@sql_mode;


@@sql_mode


ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION


-- SET sql_mode (to solve: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fiddle.t1.Product_Code' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by )
SET  sql_mode=TRADITIONAL;
SELECT 
    t1.product_code, 
    t1.product_id,
    t1.assign_key,
    GROUP_CONCAT(t2.value_name)
FROM products t1 
LEFT JOIN value t2 ON FIND_IN_SET(t2.value_id, t1.assign_key)    
GROUP BY t1.assign_key;

-- revert sql_mode 
SET sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";


product_code
product_id
assign_key
GROUP_CONCAT(t2.value_name)


GHI
2
10
null

DEF
2
10,28
null

HIJ
1
23
null

ABC
1
23,45
null

NOP
2
28
null

KLM
1
45
null


-- just adding more fields to the `GROUP BY` clause, so we do not need to change sql_mode.
SELECT 
    t1.product_code, 
    t1.product_id,
    t1.assign_key,
    GROUP_CONCAT(t2.value_name)
FROM products t1 
LEFT JOIN value t2 ON FIND_IN_SET(t2.value_id, t1.assign_key)    
GROUP BY t1.assign_key,t1.product_code,t1.product_id


product_code
product_id
assign_key
GROUP_CONCAT(t2.value_name)


GHI
2
10
null

DEF
2
10,28
null

HIJ
1
23
null

ABC
1
23,45
null

NOP
2
28
null

KLM
1
45
null


-- keep adding stuff to this query, until we have all desired values.....
SELECT 
  p.product_id,
  p.product_code,
  FIND_IN_SET(a.assign_id,p.assign_key),
  assign_key,
  SUBSTRING_INDEX(SUBSTRING_INDEX(assign_key,',',FIND_IN_SET(a.assign_id,p.assign_key)),',',-1) as v,
  a.value_id,
  v.value_name
FROM products p
INNER JOIN assign a ON a.product_id = p.product_id
INNER JOIN value v ON v.value_id = a.value_id
WHERE FIND_IN_SET(a.assign_id,p.assign_key) > 0;


product_id
product_code
FIND_IN_SET(a.assign_id,p.assign_key)
assign_key
v
value_id
value_name


1
ABC
2
23,45
45
2
Large

1
ABC
1
23,45
23
1
Red

1
HIJ
1
23
23
1
Red

1
KLM
1
45
45
2
Large

2
DEF
2
10,28
28
4
Small

2
DEF
1
10,28
10
3
Blue

2
GHI
1
10
10
3
Blue

2
NOP
1
28
28
4
Small


-- remove unneeded columns, and add GROUP_CONCAT(...)
SELECT 
  p.product_id,
  p.product_code,
  GROUP_CONCAT( SUBSTRING_INDEX(SUBSTRING_INDEX(assign_key,',',FIND_IN_SET(a.assign_id,p.assign_key)),',',-1)) as AssignIDs,
  GROUP_CONCAT( a.value_id) as ValueIDs,
  GROUP_CONCAT( v.value_name) as "Values"
FROM products p
INNER JOIN assign a ON a.product_id = p.product_id
INNER JOIN value v ON v.value_id = a.value_id
WHERE FIND_IN_SET(a.assign_id,p.assign_key) > 0
GROUP BY p.product_id, p.product_code;


product_id
product_code
AssignIDs
ValueIDs
Values


1
ABC
45,23
2,1
Large,Red

1
HIJ
23
1
Red

1
KLM
45
2
Large

2
DEF
28,10
4,3
Small,Blue

2
GHI
10
3
Blue

2
NOP
28
4
Small


-- as a bonus, the fields in GROUP_CONCAT ordered...
SELECT 
  p.product_id,
  p.product_code,
  GROUP_CONCAT( SUBSTRING_INDEX(SUBSTRING_INDEX(assign_key,',',FIND_IN_SET(a.assign_id,p.assign_key)),',',-1)
          ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(assign_key,',',FIND_IN_SET(a.assign_id,p.assign_key)),',',-1)) as AssignIDs,
  GROUP_CONCAT( a.value_id ORDER BY a.value_id) as ValueIDs,
  GROUP_CONCAT( v.value_name ORDER BY a.value_id) as "Values"
FROM products p
INNER JOIN assign a ON a.product_id = p.product_id
INNER JOIN value v ON v.value_id = a.value_id
WHERE FIND_IN_SET(a.assign_id,p.assign_key) > 0
GROUP BY p.product_id, p.product_code;


product_id
product_code
AssignIDs
ValueIDs
Values


1
ABC
23,45
1,2
Red,Large

1
HIJ
23
1
Red

1
KLM
45
2
Large

2
DEF
10,28
3,4
Blue,Small

2
GHI
10
3
Blue

2
NOP
28
4
Small

product_code	product_id	assign_key	GROUP_CONCAT(t2.value_name)
GHI	2	10	null
DEF	2	10,28	null
HIJ	1	23	null
ABC	1	23,45	null
NOP	2	28	null
KLM	1	45	null

product_id	product_code	FIND_IN_SET(a.assign_id,p.assign_key)	assign_key	v	value_id	value_name
1	ABC	2	23,45	45	2	Large
1	ABC	1	23,45	23	1	Red
1	HIJ	1	23	23	1	Red
1	KLM	1	45	45	2	Large
2	DEF	2	10,28	28	4	Small
2	DEF	1	10,28	10	3	Blue
2	GHI	1	10	10	3	Blue
2	NOP	1	28	28	4	Small

product_id	product_code	AssignIDs	ValueIDs	Values
1	ABC	45,23	2,1	Large,Red
1	HIJ	23	1	Red
1	KLM	45	2	Large
2	DEF	28,10	4,3	Small,Blue
2	GHI	10	3	Blue
2	NOP	28	4	Small

product_id	product_code	AssignIDs	ValueIDs	Values
1	ABC	23,45	1,2	Red,Large
1	HIJ	23	1	Red
1	KLM	45	2	Large
2	DEF	10,28	3,4	Blue,Small
2	GHI	10	3	Blue
2	NOP	28	4	Small