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 (SELECT `dbs`.`sku` FROM `bestseller_priority_detail` `dbs`, `bestseller_priority_master` `dbms` WHERE `dbs`.`list_id`=`dbms`.`list_id`) and pinfo.category_id in(1600027,1602754,1603441,1600082,1603443,1603444,1603702,1603446,1600088,1603445,1602665,1603447,1603448,1602376,1603413,1603415,1603728,1603414,1603417,1603461,1602054,1603418,1602511,1603179,1602782,1602191,1603433,1602788,1602784,1602667,1602796,1600029,1600051,1600278,1600155,1603449,1602308,1603451,1602545,1603419,1603714,1603420,1603422,1603704,1603423,1600230,1603425,1600256,1602947,1603427,1603428,1603727,1603429,1603430,1602379,1600031,1600017,1603431,1602380,1600307,1600114,1600296,1603452,1603453,1603456,1603457,1603455,1603458,1603459,1603460,1603454,1603436,1603437,1603438,1603439,1603440,1603463,1602762,1603464,1603465,1603472,1603468,1602481,1603469,1603470,1602502,1600072,1603473,1603700,1603474,1600265,1602472,1603475,1603476,1603477,1601866,1603466,1603467,1603478,1602705,1603479,1600133,1602473,1601908,1603480,1603481,1603482,1603717,1603709,1600268,1603484,1603485,1603486,1603487,1603488,1603489,1602046,1603490,1603492,1603493,1603494,1603495,1603496,1603491,1600104,1603497,1603502,1603498,1603499,1603500,1603501,1603503,1603732,1603504,1603505,1602764,1603506,1603507,1603508,1603178,1603509,1603327,1603359,1602922,1603515,1603519,1603517,1600128,1602739,1603520,1602642,1602156,1602746,1603521,1602410,1601845,1600177,1602516,1602154,1602413,1601849,1601846,1601848,1601847,1603522,1603523,1603524,1603525,1603526,1603527,1603528,1603041,1603529,1603530,1603510,1603511,1603512,1602306,1603513,1603514) AND `pinfo`.`status` IN ('active','discontinued','out_of_stock','spl_call_for_price') 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 '%' THEN 1 ELSE 0 END DESC, CASE WHEN MATCH(`desc_short`) AGAINST ('' IN BOOLEAN MODE) THEN 1 ELSE 0 END DESC, CASE WHEN `pinfo`.`vendor`=2 THEN IF(MATCH(`desc_long`, `manufacturer_name`, `color`) AGAINST ('' IN BOOLEAN MODE),4,0) ELSE CASE WHEN MATCH (`keywords`) AGAINST ('""' IN BOOLEAN MODE) AND MATCH (`desc_title`) AGAINST ('""' IN BOOLEAN MODE) THEN 4 ELSE IF(MATCH (`desc_title`) AGAINST ('""' IN BOOLEAN MODE),3,IF(MATCH (`keywords`) AGAINST ('""' IN BOOLEAN MODE),2,IF(MATCH(`desc_title`, `manufacturer_name`, `color`) AGAINST ('' IN BOOLEAN MODE),1,0))) END END DESC,CASE WHEN IF(`pinfo`.`vendor`=2,`category_3` ='',`category_4` ='') THEN 1 ELSE 0 END DESC, CASE WHEN `desc_short` LIKE ',%' THEN 1 ELSE 0 END DESC, SUBSTR(`pinfo`.`desc_title`, 1, LOCATE('', `pinfo`.`desc_title`)) DESC