Hoya

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 (622397, 575435, 180298, 180299, 180300, 180301, 180337, 180338, 180340, 180341, 180342, 180343, 180344, 180345, 180346, 180347, 180349, 180350, 2929, 2997, 2998, 3151, 3268, 1644, 1663, 434092, 434146, 180302, 180339, 180348)) 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,622397,575435,180298,180299,180300,180301,180337,180338,180340,180341,180342,180343,180344,180345,180346,180347,180349,180350,2929,2997,2998,3151,3268,1644,1663,434092,434146,180302,180339,180348)
View as Grid List

Items 1-30 of 95

Page
per page
Set Ascending Direction
  1. ProND 100000 Filter (16 2/3-stops)
    Hoya ProND 100000 Filter (16 2/3-stops)
    USD $99.90
    Sizes: 58mm, 67mm, 77mm, 82mm
    SKU: HOY-PRO ND 100000
    Model: XPD-58ND100000, XPD-67ND100000, XPD-77ND100000, XPD-82ND100000
    Condition: New-In-Box
  2. EVO Antistatic Protector Filter (95mm)
    Hoya EVO Antistatic Protector Filter (95mm)
    USD $139.90
    Out of stock
    SKU: HOY-09501
    Model: XEVA-95PROTEC
    Condition: New-In-Box
  3. NXT Plus UV (43mm)
    Hoya NXT Plus UV (43mm)
    USD $28.90
    SKU: HOY-04305
    Model: A-NXTPL43UV
    Condition: New-In-Box
  4. NXT Plus Circular Polarizer (43mm)
    Hoya NXT Plus Circular Polarizer (43mm)
    USD $48.90
    SKU: HOY-04306
    Model: A-NXTPL43CRPL
    Condition: New-In-Box
  5. NXT Plus UV (46mm)
    Hoya NXT Plus UV (46mm)
    USD $28.90
    SKU: HOY-04609
    Model: A-NXTPL46UV
    Condition: New-In-Box
  6. NXT Plus Circular Polarizer (46mm)
    Hoya NXT Plus Circular Polarizer (46mm)
    USD $48.90
    SKU: HOY-04610
    Model: A-NXTPL46CRPL
    Condition: New-In-Box
  7. NXT Plus Circular Polarizer (49mm)
    Hoya NXT Plus Circular Polarizer (49mm)
    USD $49.90
    SKU: HOY-04915
    Model: A-NXTPL49CRPL
    Condition: New-In-Box
  8. NXT Plus UV (52mm)
    Hoya NXT Plus UV (52mm)
    USD $28.90
    SKU: HOY-05215
    Model: A-NXTPLUV
    Condition: New-In-Box
  9. NXT Plus UV (55mm)
    Hoya NXT Plus UV (55mm)
    USD $28.90
    SKU: HOY-05514
    Model: A-NXTPL55UV
    Condition: New-In-Box
  10. NXT Plus Circular Polarizer (55mm)
    Hoya NXT Plus Circular Polarizer (55mm)
    USD $54.90
    SKU: HOY-05515
    Model: A-NXTPL55CRPL
    Condition: New-In-Box
  11. NXT Plus UV (58mm)
    Hoya NXT Plus UV (58mm)
    USD $31.90
    SKU: HOY-05816
    Model: A-NXTPL58UV
    Condition: New-In-Box
  12. NXT Plus Circular Polarizer (58mm)
    Hoya NXT Plus Circular Polarizer (58mm)
    USD $59.90
    SKU: HOY-05817
    Model: A-NXTPL58CRPL
    Condition: New-In-Box
  13. NXT Plus UV (62mm)
    Hoya NXT Plus UV (62mm)
    USD $44.90
    SKU: HOY-06215
    Model: A-NXTPL62UV
    Condition: New-In-Box
  14. NXT Plus Circular Polarizer (62mm)
    Hoya NXT Plus Circular Polarizer (62mm)
    USD $73.90
    SKU: HOY-06216
    Model: A-NXTPL62CRPL
    Condition: New-In-Box
  15. NXT Plus Circular Polarizer (67mm)
    Hoya NXT Plus Circular Polarizer (67mm)
    USD $82.90
    SKU: HOY-06717
    Model: A-NXTPLCRPL
    Condition: New-In-Box
  16. NXT Plus UV (72mm)
    Hoya NXT Plus UV (72mm)
    USD $43.99
    SKU: HOY-07217
    Model: A-NXTPL72UV
    Condition: New-In-Box
  17. NXT Plus UV (77mm)
    Hoya NXT Plus UV (77mm)
    USD $64.90
    SKU: HOY-07716
    Model: A-NXTPL77UV
    Condition: New-In-Box
  18. NXT Plus Circular Polarizer (77mm)
    Hoya NXT Plus Circular Polarizer (77mm)
    USD $112.90
    SKU: HOY-07717
    Model: A-NXTPLCRPL
    Condition: New-In-Box
  19. Hoya ND2 HMC (52mm)
    USD $26.97
    SKU: HOY-00147A
    Model: A-52ND2X-GB
    Condition: New-In-Box
  20. ND4 HMC (58mm)
    Hoya ND4 HMC (58mm)
    USD $23.95
    SKU: HOY-00213A
    Model: A-58ND4X-GB
    Condition: New-In-Box
  21. ND8 HMC (58mm)
    Hoya ND8 HMC (58mm)
    USD $31.97
    SKU: HOY-00214A
    Model: A-58ND8X-GB
    Condition: New-In-Box
  22. ND8 HMC (62mm)
    Hoya ND8 HMC (62mm)
    USD $38.97
    SKU: HOY-00305A
    Model: A-62ND8X-GB
    Condition: New-In-Box
  23. ND4 HMC (77mm)
    Hoya ND4 HMC (77mm)
    USD $65.97
    SKU: HOY-00466A
    Model: A-77ND4X-GB
    Condition: New-In-Box
  24. ND8 HMC (52mm)
    Hoya ND8 HMC (52mm)
    USD $26.97
    SKU: HOY-00150A
    Model: A-52ND8X-GB
    Condition: New-In-Box
  25. ND2 HMC (55mm)
    Hoya ND2 HMC (55mm)
    USD $28.97
    SKU: HOY-00199A
    Model: A-55ND2X-GB
    Condition: New-In-Box
  26. Alpha II Circular Polarizer
    Hoya Alpha II Circular Polarizer
    USD $33.90
    Sizes: 49mm, 52mm, 55mm, 58mm, 62mm, 67mm, 72mm, 77mm, 82mm
    SKU: HOY-ALPHA II CRPL
    Model: C-ALPII49CRPL, C-ALPII52CRPL, C-ALPII55CRPL, C-ALPII58CRPL, C-ALPII62CRPL, C-ALPII67CRPL, C-ALPII72CRPL, C-ALPII77CRPL, C-ALPII82CRPL
    Condition: New-In-Box
  27. Alpha II Protector
    Hoya Alpha II Protector
    USD $17.90
    Sizes: 49mm, 52mm, 55mm, 58mm, 62mm, 67mm, 72mm, 77mm, 82mm
    SKU: HOY-ALPHA II PROTECTOR
    Model: C-ALPII49PROT, C-ALPII52PROT, C-ALPII55PROT, C-ALPII58PROT, C-ALPII62PROT, C-ALPII67PROT, C-ALPII72PROT, C-ALPII77PROT, C-ALPII82PROT
    Condition: New-In-Box
  28. NXT Plus UV (49mm)
    Hoya NXT Plus UV (49mm)
    USD $28.90
    Out of stock
    SKU: HOY-04914
    Model: A-NXTPLUV
    Condition: New-In-Box
  29. NXT Plus Circular Polarizer (52mm)
    Hoya NXT Plus Circular Polarizer (52mm)
    USD $54.90
    Out of stock
    SKU: HOY-05216
    Model: A-NXTPL52CRPL
    Condition: New-In-Box
  30. NXT Plus Circular Polarizer (72mm)
    Hoya NXT Plus Circular Polarizer (72mm)
    USD $94.90
    Out of stock
    SKU: HOY-07218
    Model: A-NXTPL72CRPL
    Condition: New-In-Box
View as Grid List

Items 1-30 of 95

Page
per page
Set Ascending Direction