IMG Comfort
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 ((b.title=(SELECT title FROM catalogBrands WHERE id=16) AND b.title!='') OR p.brandId=16) AND (mc.piece=0 OR mc.piece IS NULL) GROUP BY p.id ORDER BY IF(mStock.inStock IS NOT NULL, mStock.inStock, p.inStock) DESC, p.displayOrder = 0, p.displayOrder ASC LIMIT 0, 20
