Уважаемые гуру! Правлю Опенкарт, возник вопрос по БД. Есть один запрос: Код: $sql = "SELECT DISTINCT ps.product_id, (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = ps.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) GROUP BY ps.product_id"; Который отбирает некоторое множество товара, в которых включена и актуальна акционная цена. Есть второй запрос: Код: $sql_offer = "SELECT * FROM " . DB_PREFIX . "product WHERE offer_marker = 1"; Которым я отбираю товары с определенным свойством. Как объединить эти запросы в один?
Ну тем методом left join,inner join,cross join зависит что вам и как вам надо взять, так как нету свойства таблицы написать запрос очень трудно, для связки нужно знать релейшины между таблицами
можно что-то в таком духе SELECT * FROM " . DB_PREFIX . "product WHERE offer_marker = 1 and ps.product_id in (тут ваш предыдущий запрос)
навскидку так $sql = "SELECT DISTINCT p.*, (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = ps.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id and p.offer_marker = 1) LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) GROUP BY ps.product_id"; хотя не понимаю в чем смысл объединения
наверное неправильно написал - это выборка под оба условия одновременно если нужны оба набора данных, то так - смотришь структуру таблицы product и пишешь запрос (набор полей свой поставь) $sql = "SELECT product_id,model,sku,upc,ean,jan,isbn,mpn,location,quantity,stock_status_id,image,manufacturer_id,shipping,price,points,tax_class_id,date_available, weight,weight_class_id,length,width,height,length_class_id,subtract,minimum,sort_order,status,viewed,date_added,date_modified, 0 as rating FROM ".DB_PREFIX."product p1 WHERE p1.offer_marker = 1 union all SELECT DISTINCT ps.product_id, 1 as model,1 as sku,1 as upc,1 as ean,1 as jan,1 as isbn,1 as mpn,1 as location,1 as quantity,1 as stock_status_id,1 as image, 1 as manufacturer_id,1 as shipping,1 as price,1 as points,1 as tax_class_id,1 as date_available,1 as weight,1 as weight_class_id,1 as length,1 as width, 1 as height,1 as length_class_id,1 as subtract,1 as minimum,1 as sort_order,1 as status,1 as viewed,1 as date_added,1 as date_modified, (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = ps.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) GROUP BY ps.product_id";
