Tamron

SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`is_salable` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '1' LEFT JOIN `mst_sorting_index` AS `mst_sorting_index_1` ON mst_sorting_index_1.product_id = e.entity_id AND mst_sorting_index_1.store_id = 1 LEFT JOIN `mst_sorting_index` AS `mst_sorting_index_0` ON mst_sorting_index_0.product_id = e.entity_id AND mst_sorting_index_0.store_id = 0 LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product')) INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = e.entity_id LEFT JOIN `inventory_stock_2` AS `stock_status_index` ON product.sku = stock_status_index.sku WHERE (e.entity_id IN (624442, 583759, 574555, 571018, 567973, 547117, 163397, 122565, 6499, 6500, 377729, 468045, 481353, 482708, 338622, 350353, 351532, 357021, 357035, 410001, 410008, 267564, 267565, 314131, 226685, 490072, 492814, 494297, 62500, 17777)) ORDER BY IFNULL(mst_sorting_index_1.factor_2_score, IFNULL(mst_sorting_index_0.factor_2_score, 0)) * 50 desc, FIELD(e.entity_id,624442,583759,574555,571018,567973,547117,163397,122565,6499,6500,377729,468045,481353,482708,338622,350353,351532,357021,357035,410001,410008,267564,267565,314131,226685,490072,492814,494297,62500,17777)
View as Grid List

Items 1-30 of 77

Page
per page
Set Ascending Direction
  1. 28-75mm f/2.8 Di III VC VXD G2 (Nikon Z)
    Tamron 28-75mm f/2.8 Di III VC VXD G2 (Nikon Z) [PREORDER]
    Special Price No Charge To Preorder Expected Retail Price of USD $999.00
    SKU: PRE-TAN-00218
    Model: AFA063Z700
    Condition: Preorder
    Mount Type: Nikon Z
  2. 150-500mm F/5-6.7 Di III VC VXD (Nikon Z)
    Tamron 150-500mm F/5-6.7 Di III VC VXD (Nikon Z)
    USD $1,199.00
    SKU: TAN-00479
    Model: AFA057Z-700
    Condition: New-In-Box
    Mount Type: Nikon Z
  3. 17-50mm f/4 Di III VXD Lens (Sony E)
    Tamron 17-50mm f/4 Di III VXD Lens (Sony E)
    USD $699.00
    SKU: TAN-00102
    Model: A068
    Condition: New-In-Box
    Mount Type: Sony E
  4. 70-180mm f/2.8 Di III VC VXD G2 (Sony E)
    Tamron 70-180mm f/2.8 Di III VC VXD G2 (Sony E)
    Special Price USD $1,199.00 After USD $100.99 Savings
    SKU: TAN-00389
    Model: AFA065S-700
    Condition: New-In-Box
    Mount Type: Sony E
  5. 35-150mm f/2-2.8 Di III VXD (Nikon Z)
    Tamron 35-150mm f/2-2.8 Di III VXD (Nikon Z)
    USD $1,999.99
    SKU: TAN-03050Z
    Model: AFA058Z-700
    Condition: New-In-Box
    Mount Type: Nikon Z
  6. 11-20mm f/2.8 Di III-A RXD (Fujfilm X)
    Tamron 11-20mm f/2.8 Di III-A RXD (Fujfilm X)
    USD $829.00
    SKU: TAN-00033
    Model: AFB060X-700
    Condition: New-In-Box
    Mount Type: Fujifilm X-Mount
  7. A034TM Tripod Collar Mount for 70-210mm f/4 Lens
    SKU: TAN-00905
    Model: A034TM
    Condition: New-In-Box
  8. HB016 Lens Hood
    Tamron HB016 Lens Hood
    USD $21.90
    SKU: TAN-00844P
    Model: HB016
    Condition: New-In-Box
  9. Tamron 82mm Lens Cap
    USD $10.95
    SKU: TAN-00807
    Model: flc82
    Condition: New-In-Box
  10. Tamron 86mm Lens Cap
    USD $10.95
    SKU: TAN-00808
    Model: FLC86
    Condition: New-In-Box
  11. 11-20mm f/2.8 Di III-A RXD (Sony E)
    Tamron 11-20mm f/2.8 Di III-A RXD (Sony E)
    Special Price USD $699.00 After USD $130.00 Savings
    SKU: TAN-00032
    Model: AFB060S-700
    Condition: New-In-Box
    Mount Type: Sony E
  12. 17-70mm f/2.8 Di III-A VC RXD (Fujifilm X)
    Tamron 17-70mm f/2.8 Di III-A VC RXD (Fujifilm X)
    USD $799.00
    SKU: TAN-00111
    Model: AFB070X-700
    Condition: New-In-Box
    Mount Type: Fujifilm X-Mount
  13. 50-400mm f/4.5-6.3 Di III VC VXD (Sony E)
    Tamron 50-400mm f/4.5-6.3 Di III VC VXD (Sony E)
    Special Price USD $1,199.00 After USD $100.00 Savings
    SKU: TAN-00297
    Model: A067
    Condition: New-In-Box
    Mount Type: Sony E
  14. 70-300mm f/4.5-6.3 Di III RXD (Nikon Z)
    Tamron 70-300mm f/4.5-6.3 Di III RXD (Nikon Z)
    Special Price USD $599.00 After USD $100.00 Savings
    SKU: TAN-03051
    Model: A047
    Condition: New-In-Box
    Mount Type: Nikon Z
  15. 70-300mm f/4.5-6.3 Di III RXD (Sony E)
    Tamron 70-300mm f/4.5-6.3 Di III RXD (Sony E)
    Special Price USD $499.00 After USD $50.00 Savings
    SKU: TAN-00432
    Model: AFA047S-700
    Condition: New-In-Box
    Mount Type: Sony E
  16. 17-70mm f/2.8 Di III-A VC RXD (Sony E)
    Tamron 17-70mm f/2.8 Di III-A VC RXD (Sony E)
    USD $699.00
    SKU: TAN-00110
    Model: AFB070S-700
    Condition: New-In-Box
    Mount Type: Sony E
  17. 18-400mm f/3.5-6.3 Di II VC HLD
    Tamron 18-400mm f/3.5-6.3 Di II VC HLD
    USD $699.00
    SKU: TAN-18-400 3.5-6.3 DI II
    Model: AFB028C-700, AFB028N-700
    Condition: New-In-Box
    Mount Types: Canon EOS (EF-S), Nikon F (DX)
  18. SP 70-200mm F/2.8 Di VC USD G2
    Tamron SP 70-200mm F/2.8 Di VC USD G2
    USD $1,299.00
    SKU: 100706
    Model: AFA025C-700, AFA025N-700
    Condition: New-In-Box
    Mount Types: Canon EOS (EF), Nikon F (FX)
  19. SP 150-600mm f/5-6.3 Di VC USD G2
    Tamron SP 150-600mm f/5-6.3 Di VC USD G2
    USD $1,399.00
    SKU: 77049
    Model: AFA022C-700, AFA022N-700
    Condition: New-In-Box
    Mount Types: Canon EOS (EF), Nikon F (FX)
  20. 35-150mm f/2-2.8 Di III VXD (Sony E)
    Tamron 35-150mm f/2-2.8 Di III VXD (Sony E)
    Special Price USD $1,799.00 After USD $100.00 Savings
    SKU: TAN-03050
    Model: AFA058S-700
    Condition: New-In-Box
    Mount Type: Sony E
  21. 28-75mm f/2.8 Di III VXD G2 (Sony E)
    Tamron 28-75mm f/2.8 Di III VXD G2 (Sony E)
    Special Price USD $799.00 After USD $100.00 Savings
    SKU: TAN-00214
    Model: AFA063S-700
    Condition: New-In-Box
    Mount Type: Sony E
  22. 24mm f/2.8 Di III OSD M1:2 (Sony E)
    Tamron 24mm f/2.8 Di III OSD M1:2 (Sony E)
    Special Price USD $199.00 After USD $50.00 Savings
    SKU: TAN-00051S
    Model: AFF051S-700
    Condition: New-In-Box
    Mount Type: Sony E
  23. 35mm f/2.8 Di III OSD M1:2 (Sony E)
    Tamron 35mm f/2.8 Di III OSD M1:2 (Sony E)
    Special Price USD $199.00 After USD $50.00 Savings
    SKU: TAN-00053S
    Model: AFF053S-700
    Condition: New-In-Box
    Mount Type: Sony E
  24. 28-200mm f/2.8-5.6 Di III RXD (Sony E)
    Tamron 28-200mm f/2.8-5.6 Di III RXD (Sony E)
    Special Price USD $699.00 After USD $100.00 Savings
    SKU: TAN-00294
    Model: AFA071S-700
    Condition: New-In-Box
    Mount Type: Sony E
  25. 17-28mm f/2.8 Di III RXD (Sony E)
    Tamron 17-28mm f/2.8 Di III RXD (Sony E)
    Special Price USD $799.00 After USD $100.00 Savings
    SKU: TAN-00079S
    Model: AFA046S-700
    Condition: New-In-Box
    Mount Type: Sony E
  26. 20-40mm f/2.8 Di III VXD (Sony E)
    Tamron 20-40mm f/2.8 Di III VXD (Sony E)
    USD $699.00
    SKU: TAN-00714
    Model: A062
    Condition: New-In-Box
    Mount Type: Sony E
  27. 18-300mm f/3.5-6.3 Di III-A VC VXD
    Tamron 18-300mm f/3.5-6.3 Di III-A VC VXD
    USD $599.00 After USD $100.00 Savings
    SKU: TAN-18-300MM F/3.5-6.3
    Model: AFB061S-700, AFB061X-700
    Condition: New-In-Box
    Available For: Sony E, Fujifilm X
  28. 150-500mm f/5-6.7 Di III VC VXD
    Tamron 150-500mm f/5-6.7 Di III VC VXD
    USD $1,199.00 After USD $200.00 Savings
    SKU: TAN-AF150-500 F/5-6.7
    Model: AFA057S700, AFA057F700
    Condition: New-In-Box
    Mount Types: Fujifilm X-Mount, Sony E
  29. 18-200mm F/3.5-6.3 Di II VC (Canon)
    Tamron 18-200mm F/3.5-6.3 Di II VC (Canon)
    USD $249.00
    Out of stock
    SKU: TAN-00128C
    Model: AFB018C-700
    Condition: New-In-Box
    Mount Type: Canon EOS (EF-S)
  30. AF 18-200mm f/3.5-6.3 DI III VC Zoom Lens (Canon EOS M) (Black)
    SKU: TAN-00131
    Model: AFB011EM-700
    Condition: New-In-Box
    Mount Type: Canon EOS (M)
View as Grid List

Items 1-30 of 77

Page
per page
Set Ascending Direction