Bassett

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 ccParent.folder='dining-room' AND ((b.title=(SELECT title FROM catalogBrands WHERE id=4) AND b.title!='') OR p.brandId=4) 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