SELECT
cscart_products.*,
cscart_product_descriptions.*,
MIN(
IF(
cscart_product_prices.percentage_discount = 0,
cscart_product_prices.price,
cscart_product_prices.price - (
cscart_product_prices.price * cscart_product_prices.percentage_discount
)/ 100
)
) as price,
GROUP_CONCAT(
CASE WHEN (
cscart_products_categories.link_type = 'M'
) THEN CONCAT(
cscart_products_categories.category_id,
'M'
) ELSE cscart_products_categories.category_id END
ORDER BY
cscart_categories.storefront_id IN (0, 1) DESC,
(
cscart_products_categories.link_type = 'M'
) DESC,
cscart_products_categories.category_position ASC,
cscart_products_categories.category_id ASC
) as category_ids,
popularity.total as popularity,
companies.company as company_name,
cscart_products.is_returnable,
cscart_products.return_period,
cscart_product_sales.amount as sales_amount,
cscart_seo_names.name as seo_name,
cscart_seo_names.path as seo_path,
cscart_discussion.type as discussion_type
FROM
cscart_products
LEFT JOIN cscart_product_prices ON cscart_product_prices.product_id = cscart_products.product_id
AND cscart_product_prices.lower_limit = 1
AND cscart_product_prices.usergroup_id IN (0)
LEFT JOIN cscart_product_descriptions ON cscart_product_descriptions.product_id = cscart_products.product_id
AND cscart_product_descriptions.lang_code = ''
LEFT JOIN cscart_companies as companies ON companies.company_id = cscart_products.company_id
INNER JOIN cscart_products_categories ON cscart_products_categories.product_id = cscart_products.product_id
INNER JOIN cscart_categories ON cscart_categories.category_id = cscart_products_categories.category_id
AND cscart_categories.storefront_id IN (0, 1)
AND (
cscart_categories.usergroup_ids = ''
)
AND (
cscart_products.usergroup_ids = ''
)
AND cscart_categories.status IN ('A', 'H')
AND cscart_products.status IN ('A', 'H')
LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = cscart_products.product_id
LEFT JOIN cscart_product_sales ON cscart_product_sales.product_id = cscart_products.product_id
AND cscart_product_sales.category_id = 370
LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 635130
AND cscart_seo_names.type = 'p'
AND cscart_seo_names.dispatch = ''
AND cscart_seo_names.lang_code = 'en'
LEFT JOIN cscart_discussion ON cscart_discussion.object_id = cscart_products.product_id
AND cscart_discussion.object_type = 'P'
WHERE
cscart_products.product_id = 635130
AND (
companies.status IN ('A')
OR cscart_products.company_id = 0
)
GROUP BY
cscart_products.product_id