- Listado de productos con sus combinaciones

SELECT

m.name AS 'Manufacturer',

p.id_product 'Product ID',

pl.name 'Product Name',

GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") AS 'Combination',

GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") AS 'Categories',

p.price 'Price',

pa.price 'Combination Price',

p.id_tax_rules_group 'VAT Group',

p.wholesale_price 'Wholesale Price',

p.reference 'Reference',

p.supplier_reference 'Supplier Reference',

p.weight 'Weight',

s.quantity 'Quantity',

pl.description_short 'Short Description',

pl.description 'Description',

pl.meta_title 'Meta Title',

pl.meta_keywords 'Meta Keywords',

pl.meta_description 'Meta Description',

pl.link_rewrite 'Link Rewrite',

pl.available_now 'Available Now',

pl.available_later 'Available Later',

p.available_for_order 'Available For Order',

p.date_add 'Date Added',

p.show_price 'Show Price',

p.online_only 'Online Only'

FROM ps_product p

LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang=2)

LEFT JOIN ps_manufacturer m ON (p.id_manufacturer = m.id_manufacturer)

LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)

LEFT JOIN ps_category c ON (cp.id_category = c.id_category)

LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category and cl.id_lang=2)

LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)

LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product and pa.id_product_attribute=s.id_product_attribute)

LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)

LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)

LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute and al.id_lang=2)

GROUP BY p.id_product,pac.id_product_attribute

order by p.id_product

 

Otros listados: