Edit History

I wrote it off the top of my head and there is a mistake.
 

 SELECT 
    pm.id_manufacturer as id_manufacturer,
    pm.name as manufacturer_name,
    p.id_product, 
    pa.reference,
    pl.name, 
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
    p.price, 
    pq.quantity
FROM ps_product p 

LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) 
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute) 
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) 
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
LEFT JOIN ps_manufacturer AS pm ON (p.id_manufacturer = pm.id_manufacturer)


WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute 

result:

I wrote it off the top of my head and there is a mistake.
 

 SELECT 
    pm.id_manufacturer as id_manufacturer,
    pm.name as manufacturer_name,
    p.id_product, 
    pa.reference,
    pl.name, 
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination, 
    p.price, 
    pq.quantity
FROM ps_product p 

LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) 
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute) 
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) 
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
LEFT JOIN ps_manufacturer AS pm ON (p.id_manufacturer = pm.id_manufacturer)


WHERE pl.id_lang = 1 
AND pal.id_lang = 1 
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute