Hi,
is it possible to build a query that generates a list of product by manufacturer? I am using PS 1.7.x
I want a list that shows basically information like:
Manufacturer 1 - Product ID - Product name - Product Category - Price excl. vat - Price incl. vat
Manufacturer 1 - Product ID - Product name - Product Category - Price excl. vat - Price incl. vat
Manufacturer 2 - Product ID - Product name - Product Category - Price excl. vat - Price incl. vat
Manufacturer 2 - Product ID - Product name - Product Category - Price excl. vat - Price incl. vat
So i tried this:
SELECT
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)
WHERE pl.id_lang = 1
AND pal.id_lang = 1
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute
Which shows me basically most of the information i need but i am missing
- Category
- Price incl. Vat
- Manufacturer
Can someone point me in the right direction please ?