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 ('5QCQZGSHXX3HIZDOSUYOSVIQ') AND pi.variation_id = '' AND p.epos_category IN ('PSYYYE5KZHLTLV7TRMWD5LYX')
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
