SELECT top 20 * from ( select top 12 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 , min(featOrder) as featOrder, min(sdesc) as sdesc,min(upc) as upc, min(sku) as sku, min(googleCat) as googleCat 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, min(CAPrescription) as isPrescription from prodGroupItems left join inventory on inventory.itemID = prodGroupItems.itemID Where CAActive = 1 and inventory.active=-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 outer apply (select top 1 googlecat from pTypesItems left join ptypes on ptypes.type_ID = ptypesItems.typeID WHERE itemID = inventory.itemID order by typeOrder) as google left join featuredprods on featuredprods.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 baseCatID = 0 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 featOrder, description

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 Products

• 
  La Roche Posay Lipikar Surgras Cleansing Bar Soap, 150g/5.25 oz

  C$10.50

• 
  Mystery Gift, 1 piece
• 
  Compressed Sponges, 2 pieces
• 
  EltaMD UV Clear Broad-Spectrum SPF 46, 48g/1.7 oz

  C$48.00