Получить все товары по id категории

229

05 апреля 2018, 13:15

Здравствуйте! Есть такой запрос к бд, который возвращает все товары:

SELECT SQL_CALC_FOUND_ROWS 
       p.product_id as 'product_id', 
       p.image as 'p.image', 
       p.price as 'p.price', 
       pd.name as 'pd.name', 
       ms.seller_id as 'seller_id', 
       ms.nickname as 'ms.nickname', 
       mp.product_status as 'mp.product_status', 
       mp.product_approved as 'mp.product_approved', 
       mp.number_sold as 'mp.number_sold', 
       mp.list_until as 'mp.list_until', 
       p.date_added as 'p.date_created', 
       p.date_modified as 'p.date_modified', 
       pd.description as 'pd.description' 
FROM oc_product p 
INNER JOIN oc_product_description pd USING(product_id)
LEFT JOIN oc_ms_product mp USING(product_id)
LEFT JOIN oc_ms_seller ms USING (seller_id) 
WHERE 1 = 1 AND ms.seller_id = 27 AND product_status IN (1) 
GROUP BY p.product_id 
HAVING 1 = 1 
ORDER BY pd.name ASC LIMIT 0, 12

Нужно обратиться к таблице oc_product_to_category (которая содержит поля product_id и category_id) и взять только те продукты у которых category_id=1, например. Не могу понять как это сделать(

Answer 1

SELECT SQL_CALC_FOUND_ROWS p.product_id as 'product_id', p.image as 
'p.image', p.price as 'p.price', pd.name as 'pd.name', ms.seller_id as 
'seller_id', ms.nickname as 'ms.nickname', mp.product_status as 
'mp.product_status', mp.product_approved as 'mp.product_approved', 
mp.number_sold as 'mp.number_sold', mp.list_until as 'mp.list_until', 
p.date_added as 'p.date_created', p.date_modified as 'p.date_modified', 
pd.description as 'pd.description' 
FROM oc_product p 
INNER JOIN oc_product_description pd USING(product_id)
LEFT JOIN oc_ms_product mp USING(product_id) 
LEFT JOIN oc_ms_seller ms USING (seller_id) 
INNER JOIN oc_product_to_category  pc USING(product_id)
WHERE ms.seller_id = 27 AND product_status IN (1) AND pc.category_id = 1
GROUP BY p.product_id 
HAVING 1 = 1 ORDER BY pd.name ASC LIMIT 0, 12

Ой как-то так, возможно, где-то ошибся

Answer 2

SELECT * FROM `oc_product_to_category` WHERE `category_id` = 1

Если нужно взять только product_id и category_id:

SELECT `product_id`, `category_id` FROM `oc_product_to_category` WHERE `category_id` = 1

Переделал твой запрос, попробуй:

SELECT SQL_CALC_FOUND_ROWS p.product_id as 'product_id', p.image as 'p.image', p.price as 'p.price', pd.name as 'pd.name', ms.seller_id as 'seller_id', ms.nickname as 'ms.nickname', mp.product_status as 'mp.product_status', mp.product_approved as 'mp.product_approved', mp.number_sold as 'mp.number_sold', mp.list_until as 'mp.list_until', p.date_added as 'p.date_created', p.date_modified as 'p.date_modified', pd.description as 'pd.description' FROM oc_product p INNER JOIN oc_product_description pd USING(product_id) LEFT JOIN oc_ms_product mp USING(product_id) LEFT JOIN oc_ms_seller ms USING (seller_id) WHERE p.category_id = 1 AND ms.seller_id = 27 AND product_status IN (1) GROUP BY p.product_id HAVING 1 = 1 ORDER BY pd.name ASC LIMIT 0, 12