Filter Your Results menu

Filters Clear All

 

Search Results Hybrid

<<1 2 >>

10" Hybrid Mattress, Plush - Full
Full$339.99
  • Twin $289.99
  • Twin XL $339.99
  • Full $339.99
  • Queen $379.99
  • King $569.99

Starting at

$ 339.99 

Arctic Hybrid - Medium - King

Store Specials

In Stock In-Stock

SELECT p.id, p.cSlug, p.SKU, IF(p.StandardColor = 'Other',CONCAT(UCASE(SUBSTRING(p.ColorDescription, 1, 1)),LCASE(SUBSTRING(p.ColorDescription, 2))),IF(p.StandardColor = 'SPLITKING', 'Split King',p.StandardColor)) AS ModelColor, mp.MSRP, mp.Sale, mp.Available, mp.Featured, IF((mp.Sale = 0), mp.MSRP, IF( (mp.MSRP = 0), mp.Sale, IF((mp.Sale > mp.MSRP), mp.MSRP, mp.Sale))) AS calcPrice, IF(mp.MSRP > 0 AND mp.MSRP > mp.Sale, mp.MSRP, '0.00') AS RegPrice, mp.OutletPrice, mp.outlet, mp.on_line, mp.in_store, mp.closeout, mp.quick_ship, mp.PowerBuy, (mp.MSRP - mp.Sale) AS Discount, IF(bd.MAP = 1 OR p.is_package = 1, 1, .90) AS MapDiscount, mp.in_stock, mp.free_shipping, p.Sale AS MinTargetPrice, p.RootModelNumber, mp.FreeShip, bd.UMRP, (SELECT COUNT(pbm.pkID) FROM PromotionsByModel AS pbm INNER JOIN Rebates ON (Rebates.PromotionName = pbm.PromotionName AND Rebates.FileName <> '') WHERE pbm.pkProduct = p.pkID AND pbm.EffectiveDate <= CURDATE() AND pbm.ExpirationDate > CURDATE() GROUP BY pbm.pkProduct) AS rwsPromotion, (SELECT COUNT(rbm.pkID) FROM rebatesbymodel AS rbm INNER JOIN Rebates AS r ON r.pkID = rbm.pkRebates WHERE rbm.pkProduct = p.pkID AND r.ExpirationDate > CURDATE() AND r.EffectiveDate < CURDATE() GROUP BY rbm.PromotionName) AS rwsRebates, (p.Sale * (IF(bd.MAP = 1 OR p.is_package = 1, 1,.90))) AS MAP, c.ConsumerDescription AS cDescription, IF(bd.no_cart =1 OR mp.on_line = 0,1,0) AS no_cart, (SELECT COUNT(*) FROM tbl_products WHERE RootModelNumber = p.RootModelNumber) AS sizeCount FROM Categories AS c INNER JOIN Category_Groups AS cg ON (c.Category_Group = cg.CategoryCode) INNER JOIN menu_heading AS mh ON (cg.MenuHeading = mh.id) INNER JOIN (SELECT tbl_products.* FROM tbl_products INNER JOIN tbl_store_brands ON tbl_store_brands.brandID = tbl_products.pkBrand AND tbl_store_brands.categoryID = tbl_products.pkCategory ORDER BY FIELD( lower( StandardColor ), 'queen', 'standard', 'twin', 'twin xl', 'Full', 'full xl', 'splitqueen', 'king', 'splitking', 'split king', 'dividedking', 'divided king', 'cal king', 'california king', 'split cal king', 'split california king' )) AS p ON (p.cSlug = c.slug AND p.cgSlug = cg.slug AND p.mhSlug = mh.slug) INNER JOIN Brand_Data AS bd ON (bd.pkID = p.pkBrand) INNER JOIN tbl_store_brands AS sb ON (sb.brandID = bd.pkID AND sb.categoryID = c.pkID) INNER JOIN tbl_fulltextsearch AS fsearch ON (fsearch.SKU = p.SKU ) INNER JOIN ModelPrice AS mp ON (mp.pkProduct = p.pkID AND mp.SKU = p.SKU AND mp.Available = 1 ) WHERE p.BaseModel IN (SELECT BaseModel FROM tbl_products WHERE SKU=?) AND (((((`p`.`cSlug` = ?) OR (`p`.`cSlug` = ?) OR (`p`.`cSlug` = ?)))((`p`.`cSlug` = ?) OR (`p`.`cSlug` = ?) OR (`p`.`cSlug` = ?))) and (((`p`.`cSlug` = ? OR `p`.`cSlug` = ? OR `p`.`cSlug` = ?)))) ORDER BY FIELD(p.StandardColor, "Twin", "Twin XL", "Full", "Full XL", "Queen", "SPLITQUEEN", "King", "SPLITKING", "California King", "Split California King"), mp.Sale ASC

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '((`p`.`cSlug` = ?) OR (`p`.`cSlug` = ?) OR (`p`.`cSlug` = ?))) and (((`p`.`cSlu' at line 1