Mattress in a Box Mattresses
Filter Results
SELECT SQL_CALC_FOUND_ROWS p.id productId, p.mcId, p.inventoryType, p.categoryId, p.brandId, IF(p.title!='',p.title,mc.title) itemTitle, mc.piece, p.msrp, p.cashPrice, p.price, p.salePrice, p.twinPrice, p.twinxlPrice, p.fullPrice, p.queenPrice, p.kingPrice, p.californiaPrice, p.splitQueenPrice, p.splitKingPrice, p.splitCaliforniaPrice, p.twinSale, p.twinxlSale, p.fullSale, p.queenSale, p.kingSale, p.californiaSale, p.splitQueenSale, p.splitKingSale, p.splitCaliforniaSale, p.saleStart, p.saleEnd, p.page, p.customizable, p.dropShip, p.dateAdded, IF(p.content!='',p.content,mc.content) content, IF(mDisplay.onDisplay IS NOT NULL, mDisplay.onDisplay, p.onDisplay) onDisplay, IF(mStock.inStock IS NOT NULL, mStock.inStock, p.inStock) inStock, p.priceProtected, cc.folder subCatFolder, cc.displayOrder, ccParent.folder catFolder, IF(b.title ='', mb.title, b.title) as brandTitle, b.hideBrand, mb.folder as brandFolder, com.id comfortId, com.comfort mattressComfort, cmedia.colorId, max(IF(ty.title='Kids', 1, 0)) kids FROM catalogProducts p LEFT JOIN `catalog`.`products` mc ON p.mcId=mc.id AND (p.mcId!='0' AND mc.id!='0') LEFT JOIN (SELECT mattressId,sizeId,MAX(onDisplay) onDisplay FROM mattressToSize GROUP BY mattressId) mDisplay ON p.id=mDisplay.mattressId LEFT JOIN (SELECT mattressId,sizeId,MAX(inStock) inStock FROM mattressToSize GROUP BY mattressId) mStock ON p.id=mStock.mattressId JOIN catalogBrands b ON p.brandId=b.id AND b.active='1' AND b.discontinued='0' LEFT JOIN `catalog`. `brands` mb ON b.mcId=mb.id AND mb.active='1' AND (mb.discontinued='0' OR IF(mDisplay.onDisplay IS NOT NULL, mDisplay.onDisplay, p.onDisplay)='1' OR IF(mStock.inStock IS NOT NULL, mStock.inStock, p.inStock)='1') JOIN catalogCategories cc ON p.categoryId = cc.id LEFT JOIN catalogCategories ccGroup ON ccGroup.id=cc.parentId AND ccGroup.parentId!=0 JOIN catalogCategories ccParent ON (ccParent.id=ccGroup.parentId) OR (ccParent.id=cc.parentId AND ccGroup.id IS NULL) LEFT JOIN `catalog`.`mattressComfort` com ON mc.comfort = com.id LEFT JOIN `catalog`.`mattressToType` mtt ON mc.id = mtt.mattressId LEFT JOIN `catalog`.`mattressTypes` mt ON mtt.typeId = mt.id LEFT JOIN `catalog`.`productsMedia` pmedia ON pmedia.productId = p.mcId LEFT JOIN `catalog`.`colorsMedia` cmedia ON cmedia.mediaId = pmedia.mediaId LEFT JOIN `catalog`.`productsTypes` pt ON p.mcId = pt.productId LEFT JOIN `catalog`.`types` ty ON ty.id=pt.typeId LEFT JOIN catalogProductsToLocation pl ON p.id = pl.productId LEFT JOIN locations l ON pl.locationId = l.id LEFT JOIN catalogBrandsPriority bp ON cc.id = bp.categoryId AND b.id = bp.brandId LEFT JOIN catalogProductsToMedia ptm ON p.id=ptm.productId WHERE p.id!='' AND cc.active=1 AND p.active=1 AND ( ( p.mcId!='0' AND mc.active='1' AND p.active='1' AND mc.piece='0' AND ( (mc.discontinued='0' AND p.discontinued='0') OR (IF(mDisplay.onDisplay IS NOT NULL, mDisplay.onDisplay, p.onDisplay)='1' OR IF(mStock.inStock IS NOT NULL, mStock.inStock, p.inStock)='1') OR (p.forceToWeb='1') ) ) OR ( p.mcId='0' AND p.active='1' AND ( (p.discontinued='0') OR (IF(mDisplay.onDisplay IS NOT NULL, mDisplay.onDisplay, p.onDisplay)='1' OR IF(mStock.inStock IS NOT NULL, mStock.inStock, p.inStock)='1') ) ) ) AND p.inventoryType = 0 AND p.categoryId=(SELECT id FROM catalogCategories WHERE folder='mattresses' AND active='1') AND (mc.piece=0 OR mc.piece IS NULL) AND (p.mcId IN (SELECT mattressId FROM `catalog`.`mattressToType` WHERE typeId = '8')) GROUP BY p.id ORDER BY p.twinPrice=0.00, IF((p.saleStart < 1675597821 AND p.saleEnd > 1675597821 AND p.twinSale > 0), p.twinSale, p.twinPrice) ASC, p.twinxlPrice=0.00, IF((p.saleStart < 1675597821 AND p.saleEnd > 1675597821 AND p.twinxlSale > 0), p.twinxlSale, p.twinxlPrice) ASC, p.fullPrice=0.00, IF((p.saleStart < 1675597821 AND p.saleEnd > 1675597821 AND p.fullSale > 0), p.fullSale, p.fullPrice) ASC, p.queenPrice=0.00, IF((p.saleStart < 1675597821 AND p.saleEnd > 1675597821 AND p.queenSale > 0), p.queenSale, p.queenPrice) ASC, p.kingPrice=0.00, IF((p.saleStart < 1675597821 AND p.saleEnd > 1675597821 AND p.kingSale > 0), p.kingSale, p.kingPrice) ASC, p.californiaPrice=0.00, IF((p.saleStart < 1675597821 AND p.saleEnd > 1675597821 AND p.californiaSale > 0), p.californiaSale, p.californiaPrice) ASC, IF(mStock.inStock IS NOT NULL, mStock.inStock, p.inStock) DESC LIMIT 0, 20
No products to show.
Mattresses
At China Towne Furniture and Mattress, we fully understand how important a good night’s sleep is. And that all starts with the right mattress! That’s why we make sure we carry the best mattress brands including Serta, iComfort, Simmons, Beautyrest, Mattress 1st, and more! Plus, we have our Support Report technology to make your decision even easier! After you’ve picked the perfect mattress, look through our large range of bedding accessories including bed frames, mattress protectors, mattress pads, pillows, and more. Don’t go one more night sleeping poorly… get that new mattress today for a better night’s sleep tonight!