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 ((`desc_short` like 'Canon Printers' OR `desc_long` like 'Canon Printers' OR `desc_title` like 'Canon Printers' OR `keywords` like 'Canon Printers' OR `category_1` like 'Canon Printers' OR `category_2` like 'Canon Printers' OR `category_3` like 'Canon Printers' OR `category_4` like 'Canon Printers' OR `manufacturer_name` like 'Canon Printers' OR `color` like 'Canon Printers') OR (MATCH(`desc_short`, `desc_long`, `desc_title`, `keywords`, `category_1`, `category_2`, `category_3`, `category_4`, `manufacturer_name`, `color`) AGAINST('+(>Canon>Canons)+(>Printers>Printers)' IN BOOLEAN MODE) OR MATCH(`desc_short`, `desc_long`, `desc_title`, `keywords`, `category_1`, `category_2`, `category_3`, `category_4`, `manufacturer_name`, `color`) AGAINST('"Canon Printers"' IN BOOLEAN MODE) OR (`pinfo`.`sku` like '%Canon Printers%' OR `pinfo`.`sku` like '%CanonPrinters%' OR (FIND_IN_SET('Canon Printers', REPLACE(`keywords`,' ;', ',')) OR FIND_IN_SET('Canon Printers', REPLACE(`keywords`,'; ', ',')) OR
FIND_IN_SET('Canon Printers', REPLACE(`keywords`,';', ',')) OR FIND_IN_SET('Canon Printers', REPLACE(`keywords`,' ,', ',')) OR FIND_IN_SET('Canon Printers', REPLACE(`keywords`,', ', ','))) ) OR (CASE WHEN `pinfo`.`vendor`=2 THEN `pinfo`.`desc_long` like 'Canon Printers' ELSE `pinfo`.`desc_title` like 'Canon Printers' END))) 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 '%Canon Printers' THEN 1 ELSE 0 END DESC, CASE WHEN MATCH(`desc_short`) AGAINST ('Canon +Printers' IN BOOLEAN MODE) THEN 1 ELSE 0 END DESC, CASE WHEN `pinfo`.`vendor`=2 THEN IF(MATCH(`desc_long`, `manufacturer_name`, `color`) AGAINST ('+Canon +Printers' IN BOOLEAN MODE),4,0) ELSE CASE WHEN MATCH (`keywords`) AGAINST ('"Canon Printers"' IN BOOLEAN MODE) AND MATCH (`desc_title`) AGAINST ('"Canon Printers"' IN BOOLEAN MODE) THEN 4 ELSE IF(MATCH (`desc_title`) AGAINST ('"Canon Printers"' IN BOOLEAN MODE),3,IF(MATCH (`keywords`) AGAINST ('"Canon Printers"' IN BOOLEAN MODE),2,IF(MATCH(`desc_title`, `manufacturer_name`, `color`) AGAINST ('+Canon +Printers' IN BOOLEAN MODE),1,0))) END END DESC,CASE WHEN IF(`pinfo`.`vendor`=2,`category_3` ='Printers',`category_4` ='Printers') THEN 1 ELSE 0 END DESC, CASE WHEN `desc_short` LIKE 'Printers,%' THEN 1 ELSE 0 END DESC, SUBSTR(`pinfo`.`desc_title`, 1, LOCATE('Printers', `pinfo`.`desc_title`)) DESC
<
GO
Currently Filtered by
Keyword
Canon Printers
Refine Search
GO
Category Filter
Copier/Fax/Multifunction Machines(18)
Inkjet Printers(7)
Toner Cartridges(3)
Laser Printers(1)
Brand
Canon(29)
Height
5.9"(3)
6.3"(3)
22.68"(2)
5.8"(2)
12.4"(2)
6"(1)
14.1"(1)
9.4"(1)
12"(1)
10"(1)
7.5"(1)
8.2"(1)
6.5"(1)
2.2"(1)
7.6"(1)
19"(1)
4.9"(1)
14.5"(1)
7.88"(1)
Color(s)
Yellow(1)
Magenta(1)
Cyan(1)
Depth
15.9"(4)
15.8"(3)
12"(2)
23.18"(2)
13.1"(1)
9.88"(1)
20.7"(1)
10.9"(1)
11.4"(1)
13.8"(1)
13"(1)
14.7"(1)
14.5"(1)
21"(1)
16.8"(1)
18.75"(1)
12.3"(1)
Filtered By Sort by
Current Page 1 of 1
Result Per Page