steveosoule
4/1/2015 - 8:09 PM

Miva - All Variants from Master Product Code

Miva - All Variants from Master Product Code

/*
	SELECT *
	FROM s01_ProductVariants pv
	LEFT JOIN s01_Attributes a
		ON pv.attr_id = a.id
	LEFT JOIN s01_Options o
		ON pv.option_id = o.id
	LEFT JOIN s01_ProductVariantParts pvp
		ON pv.variant_id = pvp.variant_id
	LEFT JOIN s01_Products p
		ON pvp.part_id = p.id
	LEFT JOIN s01_InventoryProductCounts ipc
		ON p.id = ipc.product_id
	LEFT JOIN s01_ProductImages pi
		ON p.id = pi.product_id AND pi.type_id = 1
	LEFT JOIN s01_Images i
		ON pi.image_id = i.id
	WHERE pv.product_id = (SELECT id FROM s01_Products p WHERE p.code = 'MASTER_PRODUCT_CODE');
*/


SELECT
	pv.product_id AS 'master_product_id',
	pvp.part_id AS 'variant_product_id',

	a.type AS 'attribute_type',
	a.code AS 'attribute_code',
	a.prompt AS 'attribute_prompt',

	o.code AS 'option_code',
	o.prompt AS 'option_prompt',
	o.image AS 'option_image',

	p.code AS 'variant_product_code',
	p.name AS 'variant_product_name',
	p.price AS 'variant_product_price',
	p.cost AS 'variant_product_cost',
	i.image AS 'variant_product_image',

	ipc.inventory AS 'variant_product_inventory'
FROM s01_ProductVariants pv
LEFT JOIN s01_Attributes a
	ON pv.attr_id = a.id
LEFT JOIN s01_Options o
	ON pv.option_id = o.id
LEFT JOIN s01_ProductVariantParts pvp
	ON pv.variant_id = pvp.variant_id
LEFT JOIN s01_Products p
	ON pvp.part_id = p.id
LEFT JOIN s01_InventoryProductCounts ipc
	ON p.id = ipc.product_id
LEFT JOIN s01_ProductImages pi
	ON p.id = pi.product_id AND pi.type_id = 1
LEFT JOIN s01_Images i
	ON pi.image_id = i.id
WHERE pv.product_id = (SELECT id FROM s01_Products p WHERE p.code = 'MASTER_PRODUCT_CODE');