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=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
