Refrigeration - Freezers - Icemaker: Undercounter

SELECT p.id,p.pkID, p.mhSlug,p.cgSlug,p.cSlug, p.CommonBrandName, p.RootModelNumber, p.SKU,p.SeriesName, p.StandardColor, 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,mh.super_category, mp.ge_inv 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) INNER JOIN menu_heading AS mh ON mh.slug = p.mhSlug WHERE p.mhSlug = 'refrigeration' AND cgSlug = 'freezers' AND cSlug = 'icemaker-undercounter' AND mp.Available = 1 GROUP BY p.RootModelNumber ORDER BY IF(quick_ship =1,quick_ship,999999) ASC, FIELD(p.cSlug, 'grill-accessories') ASC, p.cSlug, display_sequence ASC, IF(PowerBuy = 1,PowerBuy, 99999) ASC, preferred ASC, CommonBrandName, SKU
15
Compare Products:
  • Product List View
  • Product Grid View

Page 1 of 2
12 Last Page >>

CafeGE PROFILEIce Maker 15-Inch - Gourmet Clear Ice

Add To Compare

Cafe GE PROFILE Ice Maker 15-Inch - Gourmet Clear Ice

Model Color Price
UCC15NJII Custom Panel And Handle Required Contact us for best price
CafeGE PROFILEIce Maker 15-Inch - Nugget Ice

Add To Compare

Cafe GE PROFILE Ice Maker 15-Inch - Nugget Ice

Model Color Price
UNC15NJII Custom Panel And Handle Required Contact us for best price
Whirlpool15-inch Icemaker with Clear Ice Technology

Add To Compare

Whirlpool 15-inch Icemaker with Clear Ice Technology

Model Color Price
WUI75X15HB Black $1,699.00
WUI75X15HZ Fingerprint Resistant Stainless Steel $1899.00 $190.00 $1,709.00
Whirlpool15-inch Icemaker with Clear Ice Technology

Add To Compare

Whirlpool 15-inch Icemaker with Clear Ice Technology

Model Color Regular Price Savings After Savings
WUI95X15HZ Fingerprint Resistant Stainless Steel 2249.00*

*See price in cart

DanbyDanby Designer 1.54 lb Ice Maker

Add To Compare

Danby Danby Designer 1.54 lb Ice Maker

Model Color Price
DIM2607SSDD Black With Stainless Steel Contact us for best price
DanbyDanby 1.54 lb Ice Maker

Add To Compare

Danby Danby 1.54 lb Ice Maker

Model Color Price
DIM2607WDB White Contact us for best price
GE ProfileGE PROFILEGE Profile&trade; Opal Nugget Ice Maker

Add To Compare

GE Profile GE PROFILE GE Profile™ Opal Nugget Ice Maker

Model Color Price
OPAL01GEPKT Stainless Steel Contact us for best price
GE ProfileGE Profile&trade; Opal 2.0 Nugget Ice Maker

Add To Compare

GE Profile GE Profile™ Opal 2.0 Nugget Ice Maker

Model Color Price
XPIO13BCBT Black Stainless Contact us for best price
KitchenAidKitchenAid&reg; 18'' Automatic Ice Maker with PrintShield&trade; Finish - Stainless Steel with PrintShield&trade; Finish

Add To Compare

KitchenAid KitchenAid® 18'' Automatic Ice Maker with PrintShield™ Finish - Stainless Steel with PrintShield™ Finish

Model Color Price
KUID308HPS Stainless Steel With Printshield(tm) Finish $2,399.00
KitchenAidKitchenAid&reg; 18'' Automatic Ice Maker - Black

Add To Compare

KitchenAid KitchenAid® 18'' Automatic Ice Maker - Black

Model Color Price
KUID508HBL Black $2,599.00
KUID508HWH White $2,599.00
KitchenAidKitchenAid&reg; 18" Outdoor Automatic Ice Maker - Stainless Steel

Add To Compare

KitchenAid KitchenAid® 18" Outdoor Automatic Ice Maker - Stainless Steel

Model Color Price
KUIO338HSS Stainless Steel $2,799.00
KitchenAidKitchenAid&reg; 15'' Automatic Ice Maker - Black

Add To Compare

KitchenAid KitchenAid® 15'' Automatic Ice Maker - Black

Model Color Price
KUIX335HBL Black $2,199.00
KUIX335HWH White $2,199.00
KitchenAidKitchenAid&reg; 15'' Automatic Ice Maker with PrintShield&trade; Finish - Black Stainless Steel with PrintShield&trade; Finish

Add To Compare

KitchenAid KitchenAid® 15'' Automatic Ice Maker with PrintShield™ Finish - Black Stainless Steel with PrintShield™ Finish

Model Color Price
KUIX535HBS Black Stainless Steel With Printshield(tm) Finish $2,799.00
KUIX535HPA Panel Ready Pa 2799.00*

*See price at checkout

Page 1 of 2
12 Last Page >>

Added to Cart
Model#
Enter Zip Code*For delivery options
Final discounts, tax, and shipping costs at checkout.