1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
|
SELECT SQL_CALC_FOUND_ROWS products.product_id,
IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) AS product,
bc.country_code,
100 - ((prices.price * 100) / list_price) AS sales_discount
FROM cscart_products AS products
LEFT JOIN cscart_product_descriptions AS descr1 ON descr1.product_id = products.product_id
AND descr1.lang_code = 'fr'
LEFT JOIN cscart_product_prices AS prices ON prices.product_id = products.product_id
AND prices.lower_limit = 1
INNER JOIN cscart_products_categories AS products_categories ON products_categories.product_id = products.product_id
INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id
AND (cscart_categories.usergroup_ids = ''
OR FIND_IN_SET(0, cscart_categories.usergroup_ids)
OR FIND_IN_SET(1, cscart_categories.usergroup_ids))
AND cscart_categories.status IN ('A',
'H')
LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id
AND shared_descr.company_id = 1
AND shared_descr.lang_code = 'fr'
LEFT JOIN cscart_product_block_countries bc ON bc.product_id = products.product_id
WHERE 1
AND cscart_categories.company_id = 1
AND (products.usergroup_ids = ''
OR FIND_IN_SET(0, products.usergroup_ids)
OR FIND_IN_SET(1, products.usergroup_ids))
AND products.status IN ('A')
AND prices.usergroup_id IN (0,
0,
1)
GROUP BY products.product_id
HAVING sales_discount > 0
ORDER BY product ASC LIMIT 0,
8 |
Partager