select * from (select top 12 * from( SELECT top 40 * 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 = 154 ) 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) as temp Order by description desc) as temp2 Order by 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 Setting Spray and Powder Products
Jurlique Rose Silk Finishing Powder, 10g/0.4 oz
FACE atelier Face Finish, 118ml/4 fl oz
Institut Esthederm Protective Sunshine Care Powder, 15g/0.5 oz
Glo Skin Beauty Perfecting Powder, 3g/0.11 oz
