Armoires & Wardrobes
- Home
- Bedroom Furniture
- Armoires & Wardrobes
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='armoires-wardrobes' AND active='1') AND (mc.piece=0 OR mc.piece IS NULL) GROUP BY p.id ORDER BY p.price = 0.00, IF((p.saleStart < 1675596609 AND p.saleEnd > 1675596609 AND p.salePrice > 0), p.salePrice, p.price) ASC, p.twinPrice = 0.00, p.twinPrice ASC, p.twinxlPrice = 0.00, p.twinxlPrice ASC, p.fullPrice = 0.00, p.fullPrice ASC, p.queenPrice = 0.00, p.queenPrice ASC, p.kingPrice = 0.00, p.kingPrice ASC, p.californiaPrice = 0.00, p.californiaPrice ASC, p.splitQueenPrice = 0.00, p.splitQueenPrice ASC, p.splitKingPrice = 0.00, p.splitKingPrice ASC, p.splitCaliforniaPrice = 0.00, p.splitCaliforniaPrice ASC, IF(mStock.inStock IS NOT NULL, mStock.inStock, p.inStock) DESC LIMIT 0, 20
Armoires & Wardrobes
Armoires are the perfect bedroom furniture piece for extra clothes, jewelry or to conceal televisions for bedroom entertainment. Big or small find an Armoire to fit your bedroom and enjoy the functionality and convenience of another bedroom furniture piece.