Today Visual Changes is a recognized international leader in producing the most advanced, results-producing, corrective skin care products. As an innovator in unsurpassed product technology and outstanding product formulations, we are dedicated to constant and never-ending improvement to always bring you the very best in the skin care industry. Our goal is to empower you to achieve exceptional results and unprecedented levels of success in your quest for superb skin care. We strive for your complete satisfaction.
SELECT distinct(products.idProduct), products.sku, products.description, products.price, products.listhidden, products.listprice, products.serviceSpec, products.bToBPrice, products.imageUrl,products.noprices,products.stock, products.noStock,products.pcprod_HideBTOPrice, 0 as POrder,products.FormQuantity,products.pcProd_BackOrder,brands.IH_showprice,brands.IH_callOrder,brands.callOrderPrice,products.idbrand,disc.discountDesc, CAST(products.sdesc AS NVARCHAR(MAX)) ,products.pcProd_BTODefaultWPrice, products.pcProd_BTODefaultPrice, CASE WHEN showOrder.POrder = 0 THEN 999 WHEN showOrder.POrder is null THEN 999 ELSE showOrder.POrder END as PshowOrder FROM products left join brands on products.idbrand = brands.idbrand left join (select idproduct,pOrder from categories_products where idcategory = 1879)as showOrder on showOrder.idproduct = products.idproduct left join discountsperquantity as disc on disc.idDiscountperquantity = (Select top 1 idDiscountperquantity from discountsperquantity where discountsperquantity.idproduct = products.idproduct and discountPerUnit<>0), categories_products WHERE products.idProduct=categories_products.idProduct AND categories_products.idCategory in(1879) AND active=-1 AND configOnly=0 and removed=0 AND products.idbrand <> 152 ORDER BY products.description Asc