SELECT p.product_id,date_available, (SELECT AVG(rating) AS total FROM cartreview r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM cartproduct_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount,(SELECT price FROM cartproduct_special ps WHERE ps.product_id = p.product_id and ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start <= date_format(NOW(),'%Y-%m-%d')) AND (ps.date_end = '0000-00-00' OR ps.date_end >= date_format(NOW(),'%Y-%m-%d')) and ps.time_start<=DATE_FORMAT(NOW(),'%H:%i') and ps.time_end>=DATE_FORMAT(NOW(),'%H:%i')) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) as special,'item' AS jenis FROM cartproduct_to_category p2c LEFT JOIN cartproduct p ON (p2c.product_id = p.product_id) LEFT JOIN cartproduct_description pd ON (p.product_id = pd.product_id) LEFT JOIN cartproduct_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN cartmanufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN cartproduct_attribute AS pa ON pa.product_id = p.product_id WHERE p.status = '1' AND p.price > '0' AND p.date_available <= NOW() AND (p.date_unavailable > date_format(NOW(),'%Y-%m-%d') or p.date_unavailable='0000-00-00') AND p2s.store_id = '0' AND p2c.category_id = '40' GROUP BY p.product_id ORDER BY p.sort_order ASC, p.date_added DESCJual Memory Card Reader Dan Otg Termurah & Bergaransi – Cicilan 0% | Adaptor Reader Otg – Moganics.com