add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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