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 ('DIE60052101','DIE60224101','GMT0162','GMT0570','GMT1001','GMT11000','GMT11001','GMT11002','GMT11003','GMT11004','GMT11005','GMT11006','GMT11007','GMT11008','GMT11009','GMT1252','GMT1462','GMT14731','GMT14731CT','GMT14732','GMT14734','GMT14734CT','GMT14735','GMT14737','GMT14738','GMT14739','GMT150307','GMT150707','GMT151305','GMT151605','GMT1727','GMT192419','GMT192519','GMT192619','GMT192719','GMT192919','GMT193019','GMT4050','GMT4050CT','GMT4051','GMT40566','GMT4057','GMT40583','GMT40585','GMT4060','GMT4060CT','GMT4061','GMT4061CT','GMT4062','GMT40645','GMT4066','GMT4066CT','GMT4067','GMT4162','GMT4332','GMT4368','GMT4426','GMT4432','GMT4441','GMT4461','GMT4493','GMT4524','GMT4670','GMT4732','GMT4792','GMT5000055655','GMT5000057459','GMT5000057488','GMT5000057490','GMT5000057866','GMT5000057884','GMT5000057905','GMT5000057906','GMT5021','GMT5065','GMT5066','GMT5081','GMT5082','GMT5083','GMT5493','GMT5572','GMT57931','GMT57933','GMT57934','GMT6000','GMT6001','GMT6002','GMT6003','GMT6003CT','GMT6006','GMT6080','GMT6082','GMT6085','GMT6100','GMT6101','GMT6110','GMT6111','GMT6113','GMT6201','GMT6204','GMT6205','GMT6206','GMT6248','GMT6305','GMT6405','GMT6406','GMT6407','GMT6408','GMT6425','GMT6501','GMT6502','GMT6503','GMT6505','GMT6518','GMT6520','GMT6523','GMT6526','GMT6534','GMT6542','GMT6543','GMT6544','GMT6545','GMT6546','GMT6547','GMT6570','GMT6570CT','GMT6602','GMT6602CT','GMT6604','GMT6606','GMT6611','GMT6612','GMT6613','GMT6614','GMT6621','GMT6622','GMT6624','GMT6659','GMT6661','GMT6662','GMT6663','GMT6663CT','GMT6680','GMT6685','GMT6694','GMT6694CT','GMT6700','GMT6700CT','GMT6720','GMT6722','GMT6732','GMT6732CT','GMT6742','GMT6743','GMT6744','GMT6745','GMT6746','GMT6752','GMT6758','GMT6772','GMT6774','GMT6783','GMT6792','GMT6792CT','GMT6801','GMT6802','GMT6803','GMT6805','GMT6812','GMT6813','GMT6825','GMT6846','GMT6855','GMT6856','GMT6867','GMT6868','GMT6870','GMT6871','GMT6872','GMT6878','GMT6879','GMT6882','GMT6883','GMT6990','GMT6992','GMT6994','GMT6995','GMT6999','GMT7522','GMT7522CT','GMT7534','GMT7602','GMT7602CT','GMT7732','GMT7792','GMT93766','GMT93767','GMT93768','GMT93782','GMT93783','GMT9572','GMT9576','GMT9577','GMT96153','GMT9701','SBK11067983','SBK11067984','SBK11067985','SBK11067986','SBK11067987','SBK11068092','TWCPB4101','TWCPB4137','TWCPB4141','TWG08755','TWG08756','TWG08757','TWG09954','GMT20143','GMT20150','GMT20155','GMT21300','GMT23000','GMT23145','GMT28000','')) AND `pinfo`.`status` IN ('active','discontinued','out_of_stock') 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 '%KCX' THEN 1 ELSE 0 END DESC, CASE WHEN MATCH(`desc_short`) AGAINST ('KCX' IN BOOLEAN MODE) THEN 1 ELSE 0 END DESC, CASE WHEN `pinfo`.`vendor`=2 THEN IF(MATCH(`desc_long`, `manufacturer_name`, `color`) AGAINST ('+KCX' IN BOOLEAN MODE),4,0) ELSE CASE WHEN MATCH (`keywords`) AGAINST ('"KCX"' IN BOOLEAN MODE) AND MATCH (`desc_title`) AGAINST ('"KCX"' IN BOOLEAN MODE) THEN 4 ELSE IF(MATCH (`desc_title`) AGAINST ('"KCX"' IN BOOLEAN MODE),3,IF(MATCH (`keywords`) AGAINST ('"KCX"' IN BOOLEAN MODE),2,IF(MATCH(`desc_title`, `manufacturer_name`, `color`) AGAINST ('+KCX' IN BOOLEAN MODE),1,0))) END END DESC,CASE WHEN IF(`pinfo`.`vendor`=2,`category_3` ='KCX',`category_4` ='KCX') THEN 1 ELSE 0 END DESC, CASE WHEN `desc_short` LIKE 'KCX,%' THEN 1 ELSE 0 END DESC, SUBSTR(`pinfo`.`desc_title`, 1, LOCATE('KCX', `pinfo`.`desc_title`)) DESC