select * from (select top 14 * 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 = 27 ) 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 Nail Polish Products
T LeClerc Nail Enamel 03 - Guimauve, 5ml/0.2 fl oz
T LeClerc Nail Enamel 01 - Nacre, 5ml/0.2 fl oz
T LeClerc Nail Enamel 02 - Beige Ivoire, 5ml/0.2 fl oz
butter LONDON Nail Lacquer - Dahling, 11ml/0.4 fl oz
