SELECT p.product_id, p.price AS sprice, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM oc_product_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 oc_product_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 < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT ps.date_end FROM oc_product_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 < NOW()) AND ps.date_end != '0000-00-00' AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special_price_valid_until, pd.url FROM oc_product_to_category p2c LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2c.category_id = '223' AND p.product_id IN (252,2527,2528,5732,5733,5734,5735,5736,5737,5738,5739,5740,5741,5742,5743,5744,5745,5746,5747,5748,5749,5750,5751,5752,5753,5754,5755,5756,5757,5758,5759,7581,7582,10615,10616,10617,10618,10619,10620,10621,10622,10723,10724,10725,10726,10727,10728,10729,10730,11562,11563,11564,12104,12105,13634,13665,13757,13789,13791,13829,15886,15887,15888,15889,15890,15891,15892,15893,16030,16108,23876,23877,23878,23879,23880,23881,23882,23883,23884,24435,24436,24437,24438,24439,24440,24441,24442,24443,25684,25685,26287,26288,26289,26290,26291,26292,26293,26294,26295,26909,13150,13349,13360,13364,13368,13397,13407,13411,13412,13413,13421,13430,13432,13349,13360,13364,13368,13397,13407,13412,13413,13421,13430) GROUP BY p.product_id ORDER BY CASE WHEN p.quantity <= 0 THEN 1 ELSE 0 END, p.sort_order ASC, LCASE(pd.name) ASC LIMIT 0,10000
Dodge laikikliai ir fiksatoriai internetu pigiau | autorolis.lt