SELECT 
    p.`id_product`,
    ROUND(
        IF(
            sp.`id_specific_price` IS NOT NULL,
            IF(
                sp.`reduction_type` = "amount",
                IF(sp.`price` = -1, p.`price` - sp.`reduction`, sp.`price` - sp.`reduction`),
                IF(sp.`price` = -1, p.`price` * (1 - sp.`reduction`), sp.`price` * (1 - sp.`reduction`))
            ),
            p.`price`
        ),
        2
    ) AS final_price

FROM `ps_product` p
 INNER JOIN ps_product_shop product_shop
		ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 4)
LEFT JOIN `ps_specific_price` `sp` ON 
    sp.`id_product` = p.`id_product`
    AND sp.`id_product_attribute` = 0
    AND sp.`id_customer` = 0
    AND sp.`id_group` IN ()
    AND sp.`id_cart` = 0
    AND sp.`from` <= NOW()
    AND sp.`to` >= NOW()

LEFT JOIN `ps_product_lang` `pl` ON p.`id_product` = pl.`id_product`
 LEFT JOIN ps_stock_available stock
			ON (stock.id_product = p.id_product AND stock.id_product_attribute = 0 AND stock.id_shop_group = 1  AND stock.id_shop = 0  )
LEFT JOIN `ps_category_product` `cp` ON p.`id_product` = cp.`id_product`
WHERE (product_shop.`active` = 1 AND product_shop.saved = 1  ) AND (product_shop.`visibility` IN ("both", "catalog")) AND (pl.`id_shop` = 4 AND pl.`id_lang` = 3) AND (cp.`id_category` = 46) AND ( EXISTS(SELECT 1 FROM `ps_category_product` cp
                        JOIN `ps_category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` = 1)
                        WHERE cp.`id_product` = p.`id_product`))
ORDER BY pl.`name` ASC