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='dining-tables' 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 < 1675719491 AND p.saleEnd > 1675719491 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
Dining Tables come in so many styles, sizes, veneers or wood finishes making it possible for you to bring out your unique style that matches you and your home.
