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 = 'air-conditioners' AND cgSlug = 'dehumidifiers' AND cSlug = '31-40-pint-dehumidifier' 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
40 U.S. pint (18.9 litre) capacity per 24 hours, For areas up to 2,500 sq. ft. depending on conditions, Energy Star compliant, Electronic controls, Adjustable humidity settings
Request More Info Check for Special Offers
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 = 'air-conditioners' AND cgSlug = 'dehumidifiers' AND cSlug = '31-40-pint-dehumidifier' 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
40 U.S. pint (18.9 litre) capacity per 24 hours, For areas up to 2500 sq. ft. depending on conditions, Energy Star® compliant, Environmentally friendly R134A refrigerant, Electronic controls
Request More Info Check for Special Offers
