SELECT top 20 * from ( select min(CAID) as idProduct, min(CAprice) as price, max(groupMax) as maxPrice, min(groupMin) as minPrice, max(CAlistprice) as listPrice, min(description) as description, min(prodName) as prodName , min(inventory.CAimageUrl) as imageURL, sum(sold365) as sales, min(prodGroupItems.prodGroupID) as prodGroupID, min(gCount.groupCount) as groupCount, min(prodGroups.divider) as divider, min(prodGroups.name) as groupName, sum(count) as count , avg(avgstars) as avgstars, min(CA_callOrder) as IH_callOrder, min(CA_showPrice) as IH_ShowPrice, min(CA_CAllOrderPrice) as callOrderPrice, min(brands.idbrand) as idbrand, min(brands.brandName) as brandName, min(freezes) as freezes, min(aerosol) as aerosol, min(leaks) as leaks, min(podeclined) as podeclined, max(freeGiftID) as freeGiftID, max(CATreshHold) as treshHold FROM [dbo].[inventory] left join brands on brands.idbrand = inventory.idbrand left join prodGroupItems on prodGroupItems.itemID = inventory.itemID left join prodGroups on prodGroupItems.prodGroupID = prodGroups.prodGroupID left join (select prodGroupId, count(prodGroupID) as groupCount, min(CAprice) as groupMin, max(CAprice) as groupMax from prodGroupItems left join inventory on inventory.itemID = prodGroupItems.itemID Where CAActive = 1 group by prodGroupID) as gCount on gCount.prodGroupId = prodGroups.prodGroupID left join (SELECT itemID, COUNT(stars) AS COUNT, round(cast(SUM([stars]) as float)/COUNT([stars]) *2,0)/2 AS avgstars FROM pcReviews WHERE active=1 group by itemID) as reviews on reviews.itemID = inventory.itemID left join freegifts on freeGifts.itemID = inventory.itemID WHERE [inventory].[active]=-1 and CAID <>0 and CAActive= 1 and brands.CABlock = 0 and inventory.CABlock=0 and ((inventory.caPrescription<>1 and inventory.caPrescription<>4) or inventory.caPrescription is null) and ((inventory.usPrescription<>1 and inventory.usPrescription<>4) or inventory.usPrescription is null) AND (newItemBlock = 0 or usPrescription = 0) and brands.CAAvail = 1 AND freeGiftID is null AND exists ( SELECT itemID FROM pTypesItems WHERE inventory.itemID = pTypesItems.itemID AND pTypesItems.[active]=1 AND TYPEID = 137 ) group by CASE When prodGroupItems.prodgroupID is null then cast(newID() as varchar(255)) ELSE cast(prodGroupItems.prodgroupID as varchar(255)) END ) as tempAll Order by description
2 sizes
alera SKINCARE Healing Cream
C$32.50 to C$91.00
alera SKINCARE Zensa Topical Anaesthetic, 30g/1.1 oz
C$45.00
Alex Cosmetics Sculpt cream gel, 150ml/5.1 fl oz
C$71.00
Algologie Centella Repairing Concentrate (Gel), 75ml/2.6 fl oz
C$81.50
Algologie Firming and Tightening Body Gel, 200ml/6.8 fl oz
C$65.50
1 Sizes
Antipodes NATURAL Saviour Skin Balm
C$20.00
Aromatherapy Associates De-Stress Muscle Gel, 150ml/5 fl oz
C$44.00
Aromatherapy Associates Renewing Rose Hydrating Body Gel, 200ml/6.8 fl oz
C$64.00
Aromatherapy Associates Revive Body Gel (Cellulite), 200ml/6.8 fl oz
C$64.00
2 Sizes
Avene Cicalfate Restorative Cream
C$36.00 to C$55.00
Avene Moisturizing Melt In Balm, 250ml/8.5 fl oz
C$39.00
Avene TriXera Nutrition Nutri-Fluid Balm, 400ml/13.52 fl oz
C$55.00
Avene TriXera+ Selectiose Emollient Balm, 200ml/6.76 fl oz
C$38.00
Avene XeraCalm A.D Lipid Replenishing Balm, 200ml/6.8 fl oz
C$43.00
Babor Doctor Babor REPAIR RX Ultimate Forming Body Cream, 200ml/6.8 fl oz
C$110.00
Babor Shaping For Body - Firming Lifting Body Cream, 200ml/6.8 fl oz
C$85.00
Babor Shaping For Body - Firming Vitamin ACE Body Cream, 200ml/6.8 fl oz
C$75.00
Bella Aura Gravity Reverse Body Serum, 100ml/3.4 fl oz
C$110.00
Bioderma ABCDerm Cold Cream: Body Cream, 200ml/6.67 fl oz
C$14.00
Biodroga Performance Re-Shaping Cellulite Cream, 150ml/5.1 fl oz
C$110.00
No products found for these filters.
Please try changing some filters to see other products, or call us at 1-866-336-7546(SKIN)
You may like one of these Body Treatment Products
Blaine Labs Ortho-Nesic Pain Relieving Gel - Tube, 177ml/6 fl oz
C$34.00
La Roche Posay Lipikar Balm AP+, 400ml/13.5 fl oz
C$33.00
alera SKINCARE Zensa Topical Anaesthetic, 30g/1.1 oz
C$45.00
Hylunia Healing and Restoring Cream, 178ml/6 fl oz
Eternal Skincare is Vancouver's diamond standard in skincare and beauty's best an incredible selection of high-tech, hands-on aesthetic and medi-spa services, paired with specialty, niche, and amazingly effective beauty brands! Read More >