Запрос выбирает все модификации товаров из базы данных и представляет в виде единой таблицы (HostCMS)

query.sql

/**
	Запрос выбирает все модификации товаров из базы данных и представляет в виде единой таблицы
	
	Колонки:
		site_id — идентификатор сайта
		products_name — название товара
		mode — артикул модификации
		variants_name — название модификации
		price — основная цена модификации
		special_price — цена модификации со скидкой
		status — активность модификации
*/

SELECT
	shops.site_id as site_id,
	shop_items.name as products_name,
	modifications.marking as model,
	modifications.name AS variants_name,
	modifications.price AS price,
	modifications.price -  SUM(
		CASE
			WHEN shop_discounts.type = 1 THEN shop_discounts.value
			ELSE shop_discounts.value * modifications.price
		END
	) as special_price,
	modifications.active AS status

FROM shop_items AS modifications
	LEFT JOIN shop_items ON shop_items.id = modifications.modification_id
	LEFT JOIN shops ON shops.id = modifications.shop_id
	LEFT JOIN shop_item_discounts ON (shop_item_discounts.shop_item_id = modifications.id)
	LEFT JOIN shop_discounts ON (
			shop_discounts.id = shop_item_discounts.shop_discount_id
			AND shop_discounts.active = 1
			AND ((shop_discounts.end_datetime >= NOW() OR shop_discounts.end_datetime = '0000-00-00 00:00:00')
				AND shop_discounts.start_datetime <= NOW())
		)


WHERE modifications.modification_id <> 0
	AND modifications.deleted = 0
	AND shop_items.deleted = 0

GROUP BY modifications.id;