MySQL 8.0

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

select version();


version()


8.0.23


CREATE TABLE product_list (
id  INT, 
Product_ID VARCHAR(50), 
Product_Name  VARCHAR(50),
Supplier  VARCHAR(50));

INSERT INTO product_list VALUES
("1","A1"," product1"," company1"),
("2","A2"," product2"," company2"),
("3","A3"," product3"," company3"),
("4","A4"," product4"," company4");

CREATE TABLE product_option  (
id  INT, 
Product_ID VARCHAR(50), 
Color  VARCHAR(50),
Size VARCHAR(50));

INSERT INTO product_option VALUES
("1","A1","red","S"),
("2","A1","red","M"),
("3","A1","black","S"),
("4","A1","black","M");
SELECT
    pl.Product_ID pid,
    po.Product_ID poid,
    pl.Product_Name,
    po.Color color,
    po.Size size,
    pl.Supplier
FROM
    product_list pl
LEFT JOIN
    product_option po ON pl.Product_ID = po.Product_ID
ORDER BY
    pl.Product_ID;


pid
poid
Product_Name
color
size
Supplier


A1
A1
 product1
black
M
 company1

A1
A1
 product1
black
S
 company1

A1
A1
 product1
red
M
 company1

A1
A1
 product1
red
S
 company1

A2
null
 product2
null
null
 company2

A3
null
 product3
null
null
 company3

A4
null
 product4
null
null
 company4


SELECT
    pl.Product_ID pid,
    po.Product_ID poid,
    pl.Product_Name,
    group_concat(concat(color,' ',size) separator ', '),
    pl.supplier
FROM
    product_list pl
LEFT JOIN
    product_option po ON pl.Product_ID = po.Product_ID
GROUP BY pl.Product_ID, po.Product_ID,pl.Product_Name, pl.supplier
ORDER BY
    pl.Product_ID;
    


pid
poid
Product_Name
group_concat(concat(color,' ',size) separator ', ')
supplier


A1
A1
 product1
black M, black S, red M, red S
 company1

A2
null
 product2
null
 company2

A3
null
 product3
null
 company3

A4
null
 product4
null
 company4

pid	poid	Product_Name	color	size	Supplier
A1	A1	product1	black	M	company1
A1	A1	product1	black	S	company1
A1	A1	product1	red	M	company1
A1	A1	product1	red	S	company1
A2	null	product2	null	null	company2
A3	null	product3	null	null	company3
A4	null	product4	null	null	company4

pid	poid	Product_Name	group_concat(concat(color,' ',size) separator ', ')	supplier
A1	A1	product1	black M, black S, red M, red S	company1
A2	null	product2	null	company2
A3	null	product3	null	company3
A4	null	product4	null	company4