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 = 'refrigeration' AND cgSlug = 'side-by-side' AND cSlug = 'side-by-side-no-dispenser-refrigerators' AND mp.Available = 1 AND ((CommonBrandID = 'KTA')) 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
LED Lighting - Illuminates the inside of the refrigerator and freezer giving for better visibility. Unlike incandescent bulbs, LED lights use less energy, are maintenance-free and never need to be replaced., In-Door-Ice(R) System - Adds interior room in the refrigerator with an ice bin that mounts on the inside left door of the refrigerator, keeping it out of the refrigerator compartment and allowing for extra shelf space., Professionally-Inspired Design - Combines a bold blend of professionally-inspired styling with sleek touches for the home. This beautiful side-by-side refrigeration unit features Satin Textured Handles and the KitchenAid(R) Badge, a symbol of uncompromise, Measured Water Fill - Displays and automatically dispenses water in ounces, cups, or liters. It can be programmed to pour a measured amount, helping make food prep easier. It can even be set up to fill essential items on a daily basis like a coffee pot or, Clear Door Bins with Metallic Accents - Allows you to see labels right through the bins, helping you keep condiments and other items organized. Each bin is removable, making it easy to pull out, chop up and store an amazingly fresh produce, and simply sli
Request More Info Check for Special Offers