SELECT
p.*,
pi.filename,
(SELECT min(price) FROM product_variations WHERE epos_id = p.epos_id) AS product_price,
(SELECT count(*) FROM product_variations WHERE epos_id = p.epos_id) AS variant_count
FROM products p
LEFT JOIN product_images pi ON pi.epos_id = p.epos_id
LEFT JOIN product_variations pv ON pv.epos_id = p.epos_id
WHERE p.active = 1 AND p.epos_category NOT IN ('VQBAWWVD6ZVPL7GTJGNEQNUB', 'OHRDR22VBLW5ZOA5Y6O6L5VM', 'VBZ2ZFUYSAWCBLCCASQ3QDVM', '5QCQZGSHXX3HIZDOSUYOSVIQ') AND pi.variation_id = ''
GROUP BY p.epos_id
HAVING (SELECT SUM(stock_level) FROM product_variations WHERE epos_id = p.epos_id) > 0
ORDER BY product_price DESC, p.name ASC
Filters
