SELECT p.id,p.pkID, p.mhSlug,p.cgSlug,p.cSlug, p.CommonBrandName, p.RootModelNumber, p.SKU,p.SeriesName, p.KeyFeature1, p.KeyFeature2, p.KeyFeature3, p.KeyFeature4, p.KeyFeature5,p.client_flag ,if(p.CustomDescription IS NOT NULL AND p.CustomDescription <> '', p.CustomDescription, p.ModelDescription) AS ModelDescription,if(p.thumb LIKE 'http%',p.thumb, concat('/uploads/products/',p.thumb)) AS thumb ,if(p.large LIKE 'http%',p.large, concat('/uploads/products/',p.large)) AS large , mp.PowerBuy, mp.quick_ship, bd.preferred ,IF( (mp.Sale = 0), mp.MSRP, IF( (mp.MSRP = 0), mp.Sale, IF((mp.Sale > mp.MSRP), mp.MSRP, mp.Sale))) AS SortPrice, bd.CommonBrandID, IF( (mp.Sale = 0), mp.MSRP, IF( (mp.MSRP = 0), mp.Sale, IF((mp.Sale > mp.MSRP), mp.MSRP, mp.Sale))) AS Price, IF(mp.MSRP > 0 AND mp.MSRP > mp.Sale, mp.MSRP, '') AS RegPrice , mp.quick_ship, if(bd.cart_price_message = '' OR bd.cart_price_message IS NULL,'See price in cart', cart_price_message) AS cart_message , (mp.MSRP - mp.Sale) AS Discount, if(bd.MAP = 1, 1, .90) AS MapDiscount,mp.MSRP, bd.UMRP,p.ModelStatus,p.SKU AS ProductSKU FROM tbl_store_brands AS sb INNER JOIN Brand_Data AS bd ON bd.pkID = sb.brandID+0 INNER JOIN tbl_products AS p ON sb.categoryID = p.pkCategory AND sb.brandID = p.pkBrand INNER JOIN ModelPrice AS mp ON (p.SKU = mp.SKU AND p.pkSKU = mp.pkSKU) WHERE p.mhSlug = 'vacuums' AND cgSlug = 'vacuums' AND cSlug = 'vacuum-accessories' AND mp.Available = 1 GROUP BY p.RootModelNumber ORDER BY display_sequence ASC, IF(quick_ship =1,quick_ship,999999) ASC, IF(PowerBuy = 1,PowerBuy, 99999) ASC, preferred ASC, CommonBrandName, SKU
Attaches to most vacuums, For those difficult-to-clean areas around furniture and appliances, Dimensions: 37" x 2" x 1 1/2", Great for cleaning difficult areas such as under the fridge or around appliances and furniture, You will be redirected to Repair Clinic, which distributes Whirlpool(R) Factory Certified Parts.
Request More Info Check for Special Offers
