Banner1 Banner1
 
SELECT `pinfo`.`sku`,`pinfo`.`vendor`, IF(`pinfo`.`multi_level_pricing_check`=1,(`mlrp`.`cost`/`mlrp`.`min_qty`),IF(CURDATE() >= `ds`.`expiration_date`, `ds`.`price`, `crp`.`contract_price`)) AS `price`, CONCAT(IF(`pinfo`.`vendor`=2,`desc_long`,`desc_short`),' ',`pinfo`.`sku`,' ',`category_1`,' ',`category_2`,' ',`category_3`,' ',`category_4`,' ',IF(`pinfo`.`vendor`=2,`sales_copy`,`desc_long`),' ',IF(`dealer_sku`!='',`dealer_sku`,''),' ',`manufacturer_name`,' ',`color`) AS `search_string` FROM `product_info` `pinfo` INNER JOIN `vendor_base_costs` `vbcc` ON `pinfo`.`sku`=`vbcc`.`sku` LEFT JOIN `contract_price_1` AS `crp` ON `crp`.`sku`=`pinfo`.`sku` LEFT JOIN `bestseller_priority_detail` AS `bpd` ON `pinfo`.`sku`=`bpd`.`sku` LEFT JOIN `bestseller_priority_master` AS `bpm` ON `bpm`.`list_id`=`bpd`.`list_id` LEFT JOIN `dealer_special` AS `ds` ON `ds`.`sku`=`pinfo`.`sku` AND CURDATE() BETWEEN `ds`.`start_date` AND `ds`.`expiration_date` LEFT JOIN `multi_level_retail_price` AS `mlrp` ON `mlrp`.`sku`=`pinfo`.`sku` AND `mlrp`.`preferred_vendor`=`pinfo`.`preferred_vendor` AND (`mlrp`.`cost`/`mlrp`.`min_qty`)>0 WHERE (`pinfo`.`sku` IN ('SLY52030930','SLY52030940','SLY52030951','SLY52030961','SLY52030931','SLY52030952','SLY52030962','SLY52030932','SLY52023030','SLY52023040','SLY52023051','SLY52023061','SLY52023031','SLY52023052','SLY52023062','SLY52023130','SLY52023140','SLY52023151','SLY52023161','SLY52023131','SLY52023152','SLY52023162','SLY52023230','SLY52023240','SLY52023251','SLY52023261','SLY52023231','SLY52023252','SLY52023262','SLY52023330','SLY52023340','SLY52023351','SLY52023361','SLY52023331','SLY52023352','SLY52023362','SLY52061630','SLY52061640','SLY52061651','SLY52061661','SLY52061631','SLY52061652','SLY52061662','SLY52061632','SLY52061730','SLY52061740','SLY52061751','SLY52061761','SLY52061731','SLY52061752','SLY52061762','SLY52061732','SLY52061830','SLY52061840','SLY52061851','SLY52061861','SLY52061831','SLY52061852','SLY52061862','SLY52061832','SLY52062830','SLY52062840','SLY52062851','SLY52062861','SLY52062831','SLY52062852','SLY52062862','SLY52062832','SLY51227040','SLY51227051','SLY51227061','SLY51227031','SLY51227052','SLY51227062','SLY51227032','SLY51227240','SLY51227251','SLY51227261','SLY51227231','SLY51227252','SLY51227262','SLY51227232','SLY51874840','SLY51874851','SLY51874861','SLY51874831','SLY51874852','SLY51874862','SLY51874832','SLY51875340','SLY51875351','SLY51875361','SLY51875331','SLY51875352','SLY51875362','SLY51875332','SLY51875740','SLY51875751','SLY51875761','SLY51875731','SLY51875752','SLY51875762','SLY51875732','SLY51876340','SLY51876351','SLY51876361','SLY51876331','SLY51876352','SLY51876362','SLY51876332','SLY07850030','SLY07850040','SLY07850051','SLY07850061','SLY07850062','SLY61895730','SLY61895740','SLY61895751','SLY61895731','SLY61895732','SLY60966340','SLY60966351','SLY60966331','SLY60966332','SLY60937540','SLY60937551','SLY60937531','SLY60937532','SLY067011','SLY069526','SLY069527','')) AND `pinfo`.`status` IN ('active','discontinued') AND `pinfo`.`desc_title` != '' AND `online_status`=1 AND `custom_sku_enable`=1 AND CASE WHEN `pinfo`.`multi_level_pricing_check`=1 THEN TRUE ELSE `crp`.`contract_price`>0 END AND `pinfo`.`sku` NOT IN (SELECT `alias_sku` FROM `internal_alias_detail`) GROUP BY `pinfo`.`sku` ORDER BY ISNULL(`bpm`.`list_priority`) ASC, `bpm`.`list_priority` ASC, ISNULL(`bpd`.`priority`) ASC, `bpd`.`priority` ASC, CASE WHEN `pinfo`.`sku` LIKE '%Mattresses' THEN 1 ELSE 0 END DESC, CASE WHEN MATCH(`desc_short`) AGAINST ('Mattresses' IN BOOLEAN MODE) THEN 1 ELSE 0 END DESC, CASE WHEN `pinfo`.`vendor`=2 THEN IF(MATCH(`desc_long`, `manufacturer_name`, `color`) AGAINST ('+Mattresses' IN BOOLEAN MODE),4,0) ELSE CASE WHEN MATCH (`keywords`) AGAINST ('"Mattresses"' IN BOOLEAN MODE) AND MATCH (`desc_title`) AGAINST ('"Mattresses"' IN BOOLEAN MODE) THEN 4 ELSE IF(MATCH (`desc_title`) AGAINST ('"Mattresses"' IN BOOLEAN MODE),3,IF(MATCH (`keywords`) AGAINST ('"Mattresses"' IN BOOLEAN MODE),2,IF(MATCH(`desc_title`, `manufacturer_name`, `color`) AGAINST ('+Mattresses' IN BOOLEAN MODE),1,0))) END END DESC,CASE WHEN IF(`pinfo`.`vendor`=2,`category_3` ='Mattresses',`category_4` ='Mattresses') THEN 1 ELSE 0 END DESC, CASE WHEN `desc_short` LIKE 'Mattresses,%' THEN 1 ELSE 0 END DESC, SUBSTR(`pinfo`.`desc_title`, 1, LOCATE('Mattresses', `pinfo`.`desc_title`)) DESC

Currently Filtered by

Mattresses

Mattresses

Refine Search

  GO
 
Category

Queen(19)

Full(19)

Twin XL(18)

California King(16)

King(16)

Split Queen(15)

California Twin(14)

Twin(11)

Universal(1)

 
Brand

Sealy Master Brand(54)

Crown Jewel(28)

Stearns & Foster(28)

 
Color

Black(1)

 
Size

Queen(19)

Full(19)

Twin XL(18)

California King(17)

King(16)

Split Queen(15)

California Twin(14)

Twin(11)

Universal(1)

 
 
 
 
 

© 2017-2018 Bayshore Office Products, Inc., All Rights Reserved